package cn.com.nes.site.module.dao;

import cn.com.nes.common.util.*;
import cn.com.nes.common.util.id.IdUtils;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.PageData;
import cn.com.nes.site.module.dao.crm.CustomerDaoImpl;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionStatus;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.sql.*;
import java.util.*;

@Repository
public class ProjectDaoImplTemp extends BaseDaoImpl {
	@Resource
    private CustomerDaoImpl customerDaoImpl;
	
	private TransactionStatus newg_status = null;	
	public PageData updateProjectAuditStatus(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "t_work_order_info");
		param.put("keys", "work_order_id");
		try {
			String auditStatus=(String)param.get("auditStatus");
			String work_order_id=(String)param.get("work_order_id");
			param.put("auditTime",DBUtils.getCurTime()) ;
			param.put("auditUserId",req.getUinfo().getId()) ;
			
			if(StringUtils.isEmpty(auditStatus)) {
				res.setRetCode(9);
				res.setRetMessage("审核状态不能为空！");
			}
			if(StringUtils.isEmpty(work_order_id)) {
				res.setRetCode(9);
				res.setRetMessage("工单编号不能为空！");
			}
			res = updateTableData(newg_status, req) ;
			if(res.getRetCode()!=0) {
				rollback_newg(newg_status);
				return res ;
			}
			
			if("1".equals(auditStatus)) {
				updateProjectStatus((String)param.get("project_uuid"),"07") ;
				//插入项目完成日志
				LinkedHashMap<String, Object> e = new LinkedHashMap<String, Object>();
				e.put("tableName", "t_work_order_info");
				e.put("keys", "work_order_id");
				e.put("work_order_id", param.get("work_order_id"));
				PageData resTmp = getDetailsByKey(e) ;
				if(resTmp.getRetCode()!=0) {
					rollback_newg(newg_status);
					return resTmp ;
				}
				
				e = new LinkedHashMap<String, Object>();
				e.put("tableName", "t_project_record");
				e.put("project_uuid", (String)resTmp.getData().get("dd_id"));
				e.put("project_status", "07");
				e.put("remark", "项目完成，审核通过");
				req.setReqData(e);
				resTmp = insertTableData(newg_status, req) ;
				if(resTmp.getRetCode()!=0) {
					rollback_newg(newg_status);
					return resTmp ;
				}
			}
			
			if(res.getRetCode()==0) {
				//插入日志
				insertProjectLog(newg_status,req,work_order_id,"2","工单审核","进行了工单审核") ;
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("操作失败！");
			}
		} catch (Exception e) {
			rollback_newg(newg_status);
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	

	public PageData addProjectCheck(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "t_project_check");
		try {
			String check_no=(String)param.get("check_no");
			String check_type=(String)param.get("check_type");
			if(StringUtils.isEmpty(check_no)) {
				check_no = DBUtils.getDjbh(req.getUinfo().getCompanyId(), "CHK") ;
				param.put("check_no", check_no) ;
			}
			
			param.put("company_id", req.getUinfo().getCompanyId()) ;
			
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				res =  insertTableData(newg_status, req) ;
				//插入项目记录表
				if(res.getRetCode()!=0) {
					rollback_newg(newg_status);
					return res ;
				}	
				
				//检查之后，更改项目状态，待验收
				updateProjectStatus((String)param.get("project_uuid"),"06") ;
				
				//更新商机阶段
				if(StringUtils.isEmpty((String)param.get("contract_no"))) {
					updateOpportunityStatus((String)param.get("contract_no"),"06") ;
				}
				
				//插入日志
				String memo = "安全检查" ;
				String memoID = "3" ;
				if ("01".equals(check_type)) {
					 memo = "质量检查" ;
					 memoID = "4" ;
				}
				insertProjectLog(newg_status,req,res.getKey(),memoID,memo,"进行了"+memo) ;
	
			}else {
				param.put("keys", "uuid");
				res =  updateTableData(newg_status, req) ;
			}
			
			if(res.getRetCode()==0) {
				
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("操作失败！");
			}
		} catch (Exception e) {
			rollback_newg(newg_status);
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
			
		} finally {
			
		}
		return res;
	}
	
	public PageData deleteData(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		try {
			
			res = deleteTableData(newg_status,req) ;
			
			if(res.getRetCode()==0) {
				
				//更新商机阶段
				String tableName = (String) param.get("tableName") ;
				if("t_project".equals(tableName)) { // 若是删除项目，修改商机状态
					//生成待处理安装工单
					LinkedHashMap<String, Object> e = new LinkedHashMap<String, Object>();
					e.put("tableName", "t_project");
					e.put("keys", "uuid");
					e.put("uuid", param.get("uuid"));
					PageData resTmp = getDetailsByKey(e) ;
					if(resTmp.getRetCode()!=0) {
						rollback_newg(newg_status);
						return resTmp ;
					}
					String contract_no = (String) resTmp.getData().get("contract_no") ;
					if(!StringUtils.isEmpty(contract_no)) {
						updateOpportunityStatus(contract_no,"05") ;
					}
				}
				
				
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
			}
		} catch (Exception e) {
			rollback_newg(newg_status);
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();

		} finally {
			
		}
		return res;
	}
	
	
	public PageData addProject(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "t_project");
		try {
			String project_id=(String)param.get("project_id");
			if(StringUtils.isEmpty(project_id)) {
				param.put("project_id", DBUtils.getDjbh(req.getUinfo().getCompanyId(), "PRO")) ;
			}
			
			param.put("company_id", req.getUinfo().getCompanyId()) ;
			
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				res =  insertTableData(newg_status, req) ;
				//插入项目记录表
				if(res.getRetCode()!=0) {
					rollback_newg(newg_status);
					return res ;
				}
				
				HashMap<String,Object> e = new LinkedHashMap<String, Object>();
				e.put("tableName", "t_project_record");
				e.put("project_uuid", res.getKey());
				e.put("project_status", "01");
				e.put("remark", "待排期");
				req.setReqData(e);
				PageData resTmp = insertTableData(newg_status, req) ;
				if(resTmp.getRetCode()!=0) {
					rollback_newg(newg_status);
					return resTmp ;
				}
				
				//插入项目日志表
				insertProjectLog(newg_status,req,res.getKey(),"1","新建项目","新建一个项目") ;
				
				
			}else {
				param.put("keys", "uuid");
				res =  updateTableData(newg_status, req) ;
			}
			
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("操作失败！");
			}
		} catch (Exception e) {
			rollback_newg(newg_status);
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	
	
	public PageData getProjectHomePage(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		Map<String,Object> extData = new LinkedHashMap<String, Object>();
		try {
			//获取我的代办事项
			sb.delete(0, sb.length()) ;
			sb.append(" select b.`value` ,b.`name`,count(a.project_status) cnt from t_param b LEFT JOIN t_project a on a.project_status=b.`value` and (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')   ") ;
			
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			
			sb.append(" where b.param_type='90' ") ;
					 
			
			
			sb.append(" GROUP BY b.`value` order BY b.`value` ") ;
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			
			extData.put("topData", list) ;
			
			//获取今日动态
			sb.delete(0, sb.length()) ;
			sb.append(" select date_format(a.createTime,'%Y-%m-%d %T') createTime,a.link_id,a.link_type,a.title,b.areacode,b.username from t_project_log a INNER JOIN t_user b on a.createUid=b.id where b.companyId like concat(?,'%') ") ;
			
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"b.areacode",new String[] {"a.createUid",req.getUinfo().getId()}));
			
			sb.append("  and a.createTime >= '").append(DBUtils.getDate(-1)).append("' ");
			sb.append(" order BY a.createTime desc limit 100 ") ;
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("dynamicCnt", list.size()) ;
			extData.put("dynamic", list) ;
			
			//获取数据看板汇总数据
			sb.delete(0, sb.length()) ;
			sb.append(" select count(1) allcnt,sum(if(project_status in ('04','05','06'),1,0)) inrunning,sum(if(a.createTime>date_sub(now(), INTERVAL 30 DAY),1,0)) cnt30, sum(if(is_payment='1',1,0)) payment, sum(if(is_payment='0' and a.project_status='07' ,1,0)) notpayment, ");
			sb.append(" sum(if(b.end_time<now()  and a.project_status<'07',1,0)) out_of_date_cnt ");
			sb.append(" from t_project a LEFT JOIN t_project_scheduling b on a.uuid=b.project_uuid where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  ");
			
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			if(list.size()>0) {
				extData.put("projectInfo", list.get(0)) ;
			}
			
			//按天获取新建项目曲线图
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT date_format(a.createTime,'%Y-%m-%d') createTime,sum(if(a.project_type='01',1,0)) civilCnt,sum(if(a.project_type<>'01',1,0)) commercialCnt  from t_project a  where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  ");
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			
			
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			
			sb.append("  group by date_format(a.createTime,'%Y-%m-%d')" );
			sb.append("  order by a.createTime " );
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("newProjectLine", list) ;
			
			//按天获取回款项目曲线图
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT date_format(a.createTime,'%Y-%m-%d') createTime,sum(if(a.is_payment='1',1,0)) is_payment,sum(if(a.is_payment='0',1,0)) is_notpayment  from t_project a  where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  ");
			
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			
			sb.append("  group by date_format(a.createTime,'%Y-%m-%d')" );
			sb.append("  order by a.createTime " );
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("paymentProjectLine", list) ;
			
			
			//数据散点图
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT a.uuid project_uuid, a.charge,a.project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(a.createTime,'%Y-%m-%d %T') createTime  FROM 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90'  where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  ");
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			sb.append(" and a.latitude is not null and a.longitude is not null ");
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("mapData", list) ;			
			
			
			//施工中项目列表
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT a.uuid project_uuid,	a.project_id project_no,a.charge,a.project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(a.createTime,'%Y-%m-%d %T') createTime,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T') planned_end_time,date_format(c.start_time,'%Y-%m-%d %T') start_time,date_format(c.end_time,'%Y-%m-%d %T') end_time,b.name project_status_name,e.departmentName  FROM 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90'  LEFT JOIN t_project_scheduling c on a.uuid=c.project_uuid  LEFT JOIN t_department e on a.department_id=e.id  where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  ");
			//sb.append("  and a.area_code  like '").append(XzqhUtil.getQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode())).append("%' ");
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			sb.append(" and a.latitude is not null and a.longitude is not null and a.project_status<>'07' ");
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			
			
			sb.append(" limit 4 ");
			
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("constructioningList", list) ;	
			
			
			res.setExtData(extData);		
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	
	public PageData getProgressOverview(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		Map<String,Object> extData = new LinkedHashMap<String, Object>();
		try {
			
			String period = (String) req.getReqData().get("period") ;
			if(StringUtils.isEmpty(period)) {
				period = "9" ;
			}
			
			String days = "30" ;
			switch (period) {
			case "1":
				days = "1" ;
				break;
			case "2":
				days = "2" ;
				break;
			case "3":
				days = "7" ;
				break;
			case "4":
				days = "30" ;
				break;
			case "5":
				days = "365" ;
				break;
			default:
				break;
			}
			
			//获取我的代办事项
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT ");
			sb.append(" sum(if ((a.planned_end_time<now() and a.project_status='01'  or a.project_status='02' and b.end_time<now()),1,0)) laster_cnt, ");
			sb.append(" sum(if(a.project_status in ('04','05','06'),1,0)) running_cnt	, ");
			sb.append(" sum(if(b.end_time<now()  and a.project_status<'07',1,0)) out_of_date_cnt, ");
			sb.append("  sum(if(a.createTime>=date_sub(date(now()), INTERVAL ").append(days).append(" DAY),1,0)) cnt30, ");
			
			sb.append("  sum(if(c.createTime>date_sub(now(), INTERVAL 30 DAY),1,0)) cnt30_finish ");
			
			
			sb.append(" FROM t_project a left JOIN t_project_scheduling b on a.uuid=b.project_uuid ");
			
			sb.append("  left JOIN t_project_record c on a.uuid=c.project_uuid and c.project_status='07' ");
			
			sb.append(" where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("') ") ;
			
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));

			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			
			extData.put("topData", list) ;
			
			
			//按天获取新建项目曲线图
			sb.delete(0, sb.length()) ;
			
			sb.append(" SELECT a.uuid project_uuid, a.project_id,a.project_name,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T')  planned_end_time,date_format(b.start_time,'%Y-%m-%d %T') start_time,date_format(b.end_time,'%Y-%m-%d %T') end_time,a.project_status, ");
			sb.append(" if(a.project_status='01',datediff(now(),a.planned_end_time),datediff(now(),b.end_time) ) overDate ");
			sb.append(" from  t_project a left JOIN t_project_scheduling b on a.uuid=b.project_uuid ");
			sb.append(" where a.planned_end_time<now() and a.project_status='01'  or a.project_status<'07' and b.end_time<now() and (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  ");

			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			sb.append("  order by b.end_time,a.planned_end_time  limit 20 " );
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("outDateProjectLine", list) ;
			
			
			//施工中项目列表
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT a.uuid project_uuid,	a.project_id project_no,a.charge,a.project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(a.createTime,'%Y-%m-%d %T') createTime,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T') planned_end_time,date_format(c.start_time,'%Y-%m-%d %T') start_time,date_format(c.end_time,'%Y-%m-%d %T') end_time,b.name project_status_name ,m.name project_type_name,n.name service_mode_name,x.name project_level_name,a.project_level   FROM 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90'  ") ;
			sb.append(" INNER JOIN t_param m on a.project_type=m.`value` and m.param_type='92'  ") ;
			sb.append(" INNER JOIN t_param n on a.service_mode=n.`value` and n.param_type='91'  ") ;
			sb.append(" INNER JOIN t_param x on a.project_level=x.`value` and x.param_type='93'  ") ;
			sb.append(" LEFT JOIN t_project_scheduling c on a.uuid=c.project_uuid where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  ");
			sb.append(" and a.project_status in ('04','05','06') ");
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			sb.append(" and a.latitude is not null and a.longitude is not null ");
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			sb.append(" order by  a.createTime desc ");
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("constructioningList", list) ;	

			//待排期项目列表
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT 	a.uuid project_uuid,a.project_id project_no,a.charge,a.project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(a.createTime,'%Y-%m-%d %T') createTime,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T') planned_end_time,date_format(c.start_time,'%Y-%m-%d %T') start_time,date_format(c.end_time,'%Y-%m-%d %T') end_time,b.name project_status_name ,m.name project_type_name,n.name service_mode_name,x.name project_level_name,a.project_level   FROM 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90'  ") ;
			sb.append(" INNER JOIN t_param m on a.project_type=m.`value` and m.param_type='92'  ") ;
			sb.append(" INNER JOIN t_param n on a.service_mode=n.`value` and n.param_type='91'  ") ;
			sb.append(" INNER JOIN t_param x on a.project_level=x.`value` and x.param_type='93'  ") ;
			sb.append(" LEFT JOIN t_project_scheduling c on a.uuid=c.project_uuid where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  ");
			sb.append(" and a.project_status in ('01') ");
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			sb.append("  and a.latitude is not null and a.longitude is not null ");
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			sb.append(" order by  a.createTime desc ");
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("watingSchedulingList", list) ;	
			
			
			//待分配项目列表
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT 	a.uuid project_uuid,a.project_id project_no,a.charge,a.project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(a.createTime,'%Y-%m-%d %T') createTime,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T') planned_end_time,date_format(c.start_time,'%Y-%m-%d %T') start_time,date_format(c.end_time,'%Y-%m-%d %T') end_time,b.name project_status_name ,m.name project_type_name,n.name service_mode_name,x.name project_level_name,a.project_level   FROM 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90' ");
			
			sb.append(" INNER JOIN t_param m on a.project_type=m.`value` and m.param_type='92'  ") ;
			sb.append(" INNER JOIN t_param n on a.service_mode=n.`value` and n.param_type='91'  ") ;
			sb.append(" INNER JOIN t_param x on a.project_level=x.`value` and x.param_type='93'  ") ;
			sb.append(" LEFT JOIN t_project_scheduling c on a.uuid=c.project_uuid where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  ");
			
			sb.append(" and a.project_status in ('01','02','03','04','05','06') ");
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
		
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			sb.append(" order by  a.createTime desc ");
			
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("watingDistributionList", list) ;	
			
			
			
			res.setExtData(extData);		
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	//项目排期
	public PageData addProjectScheduling(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "t_project_scheduling");
		try {
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				res =  insertTableData(newg_status, req) ;
				if(res.getRetCode()!=0) {
					rollback_newg(newg_status);
					return res ;
				}
				
				//插入项目记录表
				HashMap<String,Object> e = new LinkedHashMap<String, Object>();
				e.put("tableName", "t_project_record");
				e.put("project_uuid", param.get("project_uuid"));
				e.put("project_status", "02");
				e.put("remark", "待分配");
				req.setReqData(e);
				PageData resTmp = insertTableData(newg_status, req) ;
				if(resTmp.getRetCode()!=0) {
					rollback_newg(newg_status);
					return resTmp ;
				}
				
				//生成待处理安装工单
				e = new LinkedHashMap<String, Object>();
				e.put("tableName", "t_project");
				e.put("keys", "uuid");
				e.put("uuid", param.get("project_uuid"));
				resTmp = getDetailsByKey(e) ;
				if(resTmp.getRetCode()!=0) {
					rollback_newg(newg_status);
					return resTmp ;
				}
				
				if(resTmp.getRetCode()==0) {
					e.put("tableName", "t_work_order_info");
					e.put("work_order_id", DBUtils.getDjbh(req.getUinfo().getCompanyId(), "WOR")) ;
					e.put("company_id", req.getUinfo().getCompanyId()) ;
					e.put("dd_id", param.get("project_uuid"));
					e.put("work_type_id", "1");
					e.put("contact", resTmp.getData().get("contact"));
					e.put("contact_phone", resTmp.getData().get("contact_phone"));
					e.put("address", resTmp.getData().get("project_address"));
					e.put("planned_start_time", resTmp.getData().get("planned_start_time"));
					e.put("planned_end_time", resTmp.getData().get("planned_end_time"));
					e.put("xzqhdm", resTmp.getData().get("area_code"));
					e.put("manufacture_id", resTmp.getData().get("manufacture_id"));
					e.put("describe", "创建安装工单");
					e.put("status", "01");
					req.setReqData(e);
					resTmp = insertTableData(newg_status, req) ;
					if(resTmp.getRetCode()!=0) {
						rollback_newg(newg_status);
						return resTmp ;
					}
					//插入工单记录
					e.put("describe", "工单待处理"); //待接单
					resTmp = addWorkOrderRecord(req) ;
					if(resTmp.getRetCode()!=0) {
						rollback_newg(newg_status);
						return resTmp ;
					}
					
					//插入项目日志表
					insertProjectLog(newg_status,req,(String) param.get("project_uuid"),"1","项目排期","对项目进行了排期") ;
					
				}
			}else {
				param.put("keys", "uuid");
				res =  updateTableData(newg_status, req) ;
			}
			if(res.getRetCode()==0) {
				//更新项目状态
				updateProjectStatus((String)param.get("project_uuid"),"02") ;
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("操作失败！");
			}
		} catch (Exception e) {
			rollback_newg(newg_status);
			e.printStackTrace();
			res.setRetCode(9);
			res.setRetMessage("处理失败");
		} finally {
			
		}
		return res;
	}
	
	public boolean updateProjectStatus(String uuid,String status) {
		try {
			this.getJdbcTemplate_newg().update("update t_project set project_status = ? where uuid=? ", new Object[] {status,uuid}) ;
			return true ;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return false ;
	}
	
	public boolean updateOpportunityStatus(String uuid,String status) {
		try {
			this.getJdbcTemplate_newg().update("update opportunity set opportunitytype = ? where uuid=? ", new Object[] {status,uuid}) ;
			return true ;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			
		}
		return false ;
	}
	
	
	public boolean insertProjectLog(TransactionStatus newg_status,DataReq req,String linkid,String linktype,String title,String remark) {
		Map<String, Object> param = req.getReqData();
		param.put("tableName", "t_project_log");
		param.put("link_id", linkid);
		param.put("link_type", linktype);
		param.put("title", title);
		param.put("remark", remark);
		insertTableData(newg_status, req) ;
		return true ;
	}
	
	
	public PageData getProjectList(DataReq req) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		StringBuffer sbfrom = new StringBuffer();
		try {
			
			//是否是看板过来
			String queryType = (String) req.getReqData().get("queryType") ;
			String period = (String) req.getReqData().get("period") ;
			if(StringUtils.isEmpty(period)) {
				period = "9" ;
			}
			
			int days = 30 ;
			switch (period) {
			case "1":
				days = 1 ;
				break;
			case "2":
				days = 2 ;
				break;
			case "3":
				days = 7 ;
				break;
			case "4":
				days = 30 ;
				break;
			case "5":
				days = 365 ;
				break;
			default:
				break;
			}
			
			if(!"9".equals(period)&&"1".equals(queryType)) {//来自于数据一览跳转
				req.getReqData().put("start_date", DBUtils.getDate(0-days)) ;
				req.getReqData().put("end_date", DBUtils.getDate(0)) ;
			}
			
			if(StringUtils.isEmpty(queryType)) {
				queryType = "1" ;
			}

			//施工中项目列表
			sb.delete(0, sb.length()) ;
			sb.append("SELECT	c.uuid scheduling_uuid,a.contract_no,a.images,a.area_code,a.project_address,a.contact,a.contact_phone,a.service_mode,a.project_type,a.project_level,a.uuid project_uuid,a.uuid,a.project_id project_no,a.charge,a.project_id project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(a.createTime,'%Y-%m-%d %T') createTime,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T') planned_end_time,date_format(c.start_time,'%Y-%m-%d %T') start_time,date_format(c.end_time,'%Y-%m-%d %T') end_time,b.name project_status_name,m.name project_type_name,n.name service_mode_name,x.name project_level_name,if(c.end_time<now() and a.project_status<>'07',1,0) isOver,a.remark,bim.bimId,a.investment_scale,a.manufacture_id,cmp.companyName manufacture_Name,cmp_dl.companyName companyName    from 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90'  ") ;
			sb.append(" INNER JOIN t_param m on a.project_type=m.`value` and m.param_type='92'  ") ;
			sb.append(" INNER JOIN t_param n on a.service_mode=n.`value` and n.param_type='91'  ") ;
			sb.append(" INNER JOIN t_param x on a.project_level=x.`value` and x.param_type='93'  ") ;
			sb.append(" LEFT JOIN t_project_scheduling c on a.uuid=c.project_uuid ");
			sb.append(" left JOIN t_project_record rec on a.uuid=rec.project_uuid and rec.project_status='07' ");
			sb.append(" LEFT JOIN opportunity_bim bim on a.contract_no=bim.opportunityId ");
			sb.append(" LEFT JOIN t_company cmp on a.manufacture_id=cmp.companyId ");
			sb.append(" INNER JOIN t_company cmp_dl on a.company_id=cmp_dl.companyId ");
			
			sb.append(" where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  ");
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			
			
			//处理跳转链接
			switch (queryType) {
			case "1"://项目总数
				
				break;
			case "2"://在建项目
				sb.append("  and a.project_status in ('04','05','06') ") ;
				break;
			case "3"://本月新增数  
				sb.append("  and a.createTime>date_sub(now(), INTERVAL 30 DAY) ") ;
				break;
			case "4"://本月回款项目数
				sb.append("  and a.is_payment='1' ") ;
				break;
			case "5"://验收未回款项目   
				sb.append("  and a.is_payment='0' and a.project_status='07' ") ;
				break;
			case "6"://已延期项目 
				sb.append("  and c.end_time<now()  and a.project_status<'07' ") ;
				break;
			case "7"://已完成项目 
				sb.append("  and a.project_status in ('07')  ") ;
				break;
			case "8"://应开始未开始项目 
				sb.append("  and (a.planned_end_time<now() and a.project_status='01'  or a.project_status='02' and c.end_time<now())  ") ;
				break;
			case "9"://已超期项目
				sb.append("  and c.end_time<now()  and a.project_status<'07'  ") ;
				break;
			case "10"://本月已完成项目  
				sb.append(" and rec.createTime>date_sub(now(), INTERVAL 30 DAY) ") ;
				break;
			default:
				break;
			}
			//项目名称
			if(!StringUtils.isEmpty((String)req.getReqData().get("project_name"))) {
				sb.append("  and a.project_name like  '%").append(req.getReqData().get("project_name")).append("%' ");
			}
			
			//项目类型
			if(!StringUtils.isEmpty((String)req.getReqData().get("project_type"))) {
				sb.append("  and a.project_type =  '").append(req.getReqData().get("project_type")).append("' ");
			}
			
			
			//时间
			if(!StringUtils.isEmpty((String)req.getReqData().get("start_date"))) {
				sb.append("  and a.createTime >=  '").append(req.getReqData().get("start_date")).append("' ");
			}
			if(!StringUtils.isEmpty((String)req.getReqData().get("end_date"))) {
				sb.append("  and a.createTime <=  '").append(req.getReqData().get("end_date")).append(" 23:59:59' ");
			}
			
			//项目状态
			if(!StringUtils.isEmpty((String)req.getReqData().get("project_status"))) {
				sb.append("  and a.project_status =  '").append(req.getReqData().get("project_status")).append("' ");
			}
			
			
			sbfrom.append(sb.toString());
			int idx = sbfrom.indexOf(" from ");
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(), req.getPageid(), req.getPagesize(), res,new Object[] { req.getUinfo().getCompanyId() });
			sb.append(" order by  a.createTime desc ");
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			
			
			for(int i=0;i<list.size();i++) {
				String images = (String)list.get(i).get("images") ;
				if(!StringUtils.isEmpty(images)) {
					String[] imageList = images.split(";",-1) ;
					List<String> imgURL = new ArrayList<String>() ;
					for(int k=0;k<imageList.length;k++) {
						String str = imageList[k] ;
						imgURL.add(OssUtil.getImgSignURL(str, 0)) ;
						
					}
					list.get(i).put("imageList", imgURL) ;
				}
			}
			
			res.setPage(list);	
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getSchedulingList(DataReq req) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		StringBuffer sbfrom = new StringBuffer();
		try {
			if(StringUtils.isEmpty( (String) req.getReqData().get("start_date"))) {
				req.getReqData().put("start_date",DBUtils.getDate(0)) ;
			}
			
			if(StringUtils.isEmpty( (String) req.getReqData().get("end_date"))) {
				req.getReqData().put("end_date",DBUtils.getDate(+2)) ;
			}
			
			//施工中项目列表
			sb.delete(0, sb.length()) ;
			sb.append("SELECT 	a.uuid,a.project_id project_no,a.charge,a.id project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(a.createTime,'%Y-%m-%d %T') createTime,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T') planned_end_time,date_format(c.start_time,'%Y-%m-%d %T') start_time,date_format(c.end_time,'%Y-%m-%d %T') end_time,b.name project_status_name,m.name project_type_name,n.name service_mode_name,x.name project_level_name   from 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90'  ") ;
			sb.append(" INNER JOIN t_param m on a.project_type=m.`value` and m.param_type='92'  ") ;
			sb.append(" INNER JOIN t_param n on a.service_mode=n.`value` and n.param_type='91'  ") ;
			sb.append(" INNER JOIN t_param x on a.project_level=x.`value` and x.param_type='93'  ") ;
			sb.append(" INNER JOIN t_project_scheduling c on a.uuid=c.project_uuid where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  ");
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			
			//项目名称
			if(!StringUtils.isEmpty((String)req.getReqData().get("project_name"))) {
				sb.append("  and a.project_name like  '%").append(req.getReqData().get("project_name")).append("%' ");
			}
			
			//项目类型
			if(!StringUtils.isEmpty((String)req.getReqData().get("project_type"))) {
				sb.append("  and a.project_type =  '").append(req.getReqData().get("project_type")).append("' ");
			}
			
			
			//时间
			if(!StringUtils.isEmpty((String)req.getReqData().get("start_time"))) {
				sb.append("  and c.start_time >=  '").append(req.getReqData().get("start_time")).append("'  ");
			}
			if(!StringUtils.isEmpty((String)req.getReqData().get("end_time"))) {
				sb.append("  and c.end_time <=  '").append(DBUtils.getDate(DBUtils.StrToDate((String)req.getReqData().get("end_time")), 1)).append("' ");
			}
			
			//项目状态
			if(!StringUtils.isEmpty((String)req.getReqData().get("project_status"))) {
				sb.append("  and a.project_status =  '").append(req.getReqData().get("project_status")).append("' ");
			}
			
			//项目状态
			if(!StringUtils.isEmpty((String)req.getReqData().get("department_id"))) {
				sb.append("  and c.department_id =  '").append(req.getReqData().get("department_id")).append("' ");
			}
			
			sbfrom.append(sb.toString());
			int idx = sbfrom.indexOf(" from ");
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(), req.getPageid(), req.getPagesize(), res,new Object[] { req.getUinfo().getCompanyId() });
			sb.append(" order by  a.createTime desc ");
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			
			List<Map<String, Object>> row = new ArrayList<Map<String,Object>>() ;
			
			String sTime = (String) req.getReqData().get("start_date") ;
			String eTime = (String) req.getReqData().get("end_date") ;

			
			for(int i=0;i<list.size();i++) {
				Map<String, Object> e = new LinkedHashMap<String, Object>() ;
				e.put("label",list.get(i).get("project_name")) ;
				e.put("uuid", list.get(i).get("uuid")) ;
				Map<String, Object> e1 = new LinkedHashMap<String, Object>() ;
				Map<String, Object> e2 = new LinkedHashMap<String, Object>() ;
				e1.put("myStart", list.get(i).get("start_time")) ;
				e1.put("myEnd", list.get(i).get("end_time")) ;
				e1.put("label", list.get(i).get("project_name")) ;
				
				e2.put("immobile", true) ;
				e2.put("handles", false) ;
				e2.put("color", "#ffffff") ;
				e2.put("backgroundColor", "#6699ff") ;
				e2.put("borderRadius", 0) ;
				e1.put("ganttBarConfig", e2) ;
				
				List<Object> e1_list = new ArrayList<Object>() ;
				e1_list.add(e1);
				e.put("bars",e1_list) ;
				row.add(e);
				
			}
			
			Map<String, Object> extra  = new LinkedHashMap<String, Object>();
			List<String> datelist = new ArrayList<String>();
			datelist.add("") ;
			for(int i=0;i<50;i++) {
				String tmp = DBUtils.getDate(DBUtils.StrToDate(sTime), i);
				if(tmp.compareToIgnoreCase(eTime)<=0) {
					datelist.add(tmp);
				}else {
					break ;
				}
				
			}
			
			extra.put("rows", row) ;
			
			extra.put("list", datelist) ;
			extra.put("myChartStart", sTime) ;
			extra.put("myChartEnd", DBUtils.getDate(DBUtils.StrToDate(eTime), 1)) ;
	
			res.setExtData(extra);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	//获取待分配，已分配工单列表
	public PageData getAllocatedList(DataReq req) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		StringBuffer sbfrom = new StringBuffer();
		try {
			
			//施工中项目列表
			sb.delete(0, sb.length()) ;
			sb.append("SELECT 	a.contact,a.contact_phone,d.work_order_id,a.uuid,a.project_id project_no,a.project_address,a.charge,a.id project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(a.createTime,'%Y-%m-%d %T') createTime,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T') planned_end_time,date_format(c.start_time,'%Y-%m-%d %T') start_time,date_format(c.end_time,'%Y-%m-%d %T') end_time,b.name project_status_name,m.name project_type_name,n.name service_mode_name,x.name project_level_name,date_format(d.createTime,'%Y-%m-%d %T')  workorder_time,f.NAME workorder_status,g.username maintain_name  from 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90'  ") ;
			sb.append(" INNER JOIN t_param m on a.project_type=m.`value` and m.param_type='92'  ") ;
			sb.append(" INNER JOIN t_param n on a.service_mode=n.`value` and n.param_type='91'  ") ;
			sb.append(" INNER JOIN t_param x on a.project_level=x.`value` and x.param_type='93'  ") ;
			sb.append(" INNER JOIN t_work_order_info  d ON d.dd_id = a.uuid  ") ;
			sb.append(" INNER JOIN t_param f on  d.`status` = f.`value`  AND f.param_type = '94'  ") ;
			sb.append(" LEFT JOIN t_user g on  g.id = d.maintain_id  ") ;
			
			
			sb.append(" INNER JOIN t_project_scheduling c on a.uuid=c.project_uuid where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  ");

			
			
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			
			//排期之后的项目
			sb.append("  and a.project_status =  '02' ");
			
			
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			
			//项目名称
			if(!StringUtils.isEmpty((String)req.getReqData().get("project_name"))) {
				sb.append("  and a.project_name like  '%").append(req.getReqData().get("project_name")).append("%' ");
			}
			
			//项目类型
			if(!StringUtils.isEmpty((String)req.getReqData().get("project_type"))) {
				sb.append("  and a.project_type =  '").append(req.getReqData().get("project_type")).append("' ");
			}
			
			
			//时间
			if(!StringUtils.isEmpty((String)req.getReqData().get("start_time"))) {
				sb.append("  and c.start_time <=  '").append(req.getReqData().get("start_time")).append(" 23:59:59'");
			}
			if(!StringUtils.isEmpty((String)req.getReqData().get("end_time"))) {
				sb.append("  and c.end_time >=  '").append(req.getReqData().get("end_time")).append("' ");
			}
			
			//项目状态
			if(!StringUtils.isEmpty((String)req.getReqData().get("project_status"))) {
				sb.append("  and a.project_status =  '").append(req.getReqData().get("project_status")).append("' ");
			}
			
			//项目状态
			if(!StringUtils.isEmpty((String)req.getReqData().get("department_id"))) {
				sb.append("  and c.department_id =  '").append(req.getReqData().get("department_id")).append("' ");
			}
			
			sbfrom.append(sb.toString());
			int idx = sbfrom.indexOf(" from ");
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(), req.getPageid(), req.getPagesize(), res,new Object[] { req.getUinfo().getCompanyId() });
			sb.append(" order by  a.createTime desc ");
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			res.setPage(list);	
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getInstallUsers(DataReq req) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		try {
			String sTime = (String) req.getReqData().get("start_date") ;
			String eTime = (String) req.getReqData().get("end_date") ;
			if(StringUtils.isEmpty(sTime)) {
				sTime = DBUtils.getDate(0) ;
				eTime = DBUtils.getDate(3) ;
			}
			
			sb.append("SELECT a.id,a.uuid,a.nickName,min(date_format(b.planned_start_time,'%Y-%m-%d %T')) start_date,max(date_format(b.planned_end_time,'%Y-%m-%d %T')) end_date,count(b.work_order_id) cnt from t_user a ");
			sb.append(" LEFT JOIN t_work_order_info b on a.id=b.maintain_id  and b.status in ('02','04','05') ") ; //接单，拒单，到达3个状态
			sb.append("  where a.sfsc=0 and a.userType='01' ");
			sb.append("  and a.companyId = ? ");
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.areacode",new String[] {"a.createUid",req.getUinfo().getId()}));
			
			//部门ID
			if(!StringUtils.isEmpty((String)req.getReqData().get("departmentId"))) {
				sb.append("  and a.departmentId =  '").append(req.getReqData().get("departmentId")).append("' ");
			}
			
			sb.append("  group by a.id,\r\n" + 
					"	a.uuid,\r\n" + 
					"	a.nickName order by  a.nickName desc ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			
			
			//格式转化
			List<Map<String, Object>> row = new ArrayList<Map<String,Object>>() ;

			
			for(int i=0;i<list.size();i++) {
				Map<String, Object> e = new LinkedHashMap<String, Object>() ;
				if(StringUtils.isEmpty((String) list.get(i).get("start_date"))) {
					list.get(i).put("start_date",DBUtils.getDate(0)) ;
				}
				if(StringUtils.isEmpty((String) list.get(i).get("end_date"))) {
					list.get(i).put("end_date",DBUtils.getDate(0)) ;
				}
				
				
				e.put("label",list.get(i).get("nickName") +"("+list.get(i).get("cnt")+")") ;
				e.put("id", list.get(i).get("id")) ;
				Map<String, Object> e1 = new LinkedHashMap<String, Object>() ;
				Map<String, Object> e2 = new LinkedHashMap<String, Object>() ;
				e1.put("myStart", list.get(i).get("start_date")) ;
				e1.put("myEnd", list.get(i).get("end_date")) ;
				e1.put("label", list.get(i).get("nickName")) ;
				
				
				
				e2.put("immobile", true) ;
				e2.put("handles", false) ;
				e2.put("color", "#ffffff") ;
				e2.put("backgroundColor", "#6699ff") ;
				e2.put("borderRadius", 0) ;
				e1.put("ganttBarConfig", e2) ;
				
				List<Object> e1_list = new ArrayList<Object>() ;
				e1_list.add(e1);
				e.put("bars",e1_list) ;
				row.add(e);
				
			}
			
			Map<String, Object> extra  = new LinkedHashMap<String, Object>();
			List<String> datelist = new ArrayList<String>();
			datelist.add("") ;
			for(int i=0;i<50;i++) {
				String tmp = DBUtils.getDate(DBUtils.StrToDate(sTime), i);
				if(tmp.compareToIgnoreCase(eTime)<=0) {
					datelist.add(tmp);
				}else {
					break ;
				}
				
			}
			
			extra.put("rows", row) ;
			extra.put("list", datelist) ;
			extra.put("myChartStart", sTime) ;
			extra.put("myChartEnd", DBUtils.getDate(DBUtils.StrToDate(eTime), 1)) ;
			
			res.setPage(list);	
			res.setExtData(extra); 
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData addWorkOrder(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "t_work_order_info");
		param.put("company_id", req.getUinfo().getCompanyId()) ;
		param.put("status", "02"); //待接单
		param.put("work_type_id", "1"); 
		
		try {
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				param.put("work_order_id", DBUtils.getDjbh(req.getUinfo().getCompanyId(), "WOR")) ;
				res =  insertTableData(newg_status, req) ;
				param.put("addData", "1");
			}else {
				param.put("keys", "uuid");
				res =  updateTableData(newg_status, req) ;
				param.put("addData", "0");
			}
			
			if(res.getRetCode()==0) {
				//插入记录表
				param.put("describe", "安装已派单"); //待接单
				res = addWorkOrderRecord(req) ;
				if(res.getRetCode()==0) {
					commit_newg(newg_status);
					res.setRetCode(0);
					res.setRetMessage("OK");
					res.setKey((String)param.get("uuid")) ;
				}else{
					rollback_newg(newg_status);
					res.setRetCode(9);
					res.setRetMessage("操作失败！");
				}
				
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("操作失败！");
			}
		} catch (Exception e) {
			rollback_newg(newg_status);
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	
	public PageData addWorkOrderRecord(DataReq req) throws SQLException {
		PageData res = new PageData();
		Map<String, Object> param = req.getReqData();
		param.put("record_id", DBUtils.getDjbh(req.getUinfo().getCompanyId(), "REC")) ;
		param.put("tableName", "t_work_order_record");
		try {
			if("0".equals(param.get("addData"))){
				//res = updateTableData(newg_status, req) ;
				res.setRetCode(0);
			}else {
				res = insertTableData(newg_status, req) ;
			}
			
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	
	public PageData changeOrderStatus(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "t_work_order_info");
		param.put("company_id", req.getUinfo().getCompanyId()) ;
		String woder_status = (String) param.get("status") ;
		String planned_start_time = (String) param.get("planned_start_time") ;
		
		try {
			param.put("keys", "work_order_id");
			if("02".equals(woder_status)) { //如果是派单,将计划时间改为当前时间
				if(StringUtils.isEmpty(planned_start_time)) {
					param.put("planned_start_time", DBUtils.getDate(0));
					param.put("planned_end_time", DBUtils.getDate(1));
				}
				
			}
			
			res =  updateTableData(newg_status, req) ;
			
			if(res.getRetCode()==0) {
				insertProjectLog(newg_status,req,(String)param.get("work_order_id"),"2","工单状态变更","更新了工单状态") ;
				//插入记录表
				res = addWorkOrderRecord(req) ;
				if(res.getRetCode()==0) {
					//更改项目表状态
					
					
					
					String project_status = "00" ;
					String project_status_name = "" ;
					if("02".equals(woder_status)) { //如果工单状态是派单，那么项目状态改为待施工
						project_status = "03" ;
						project_status_name = "待施工" ;
					}
					
					if("05".equals(woder_status)) { //如果工单状态为已到达，将项目状态改为施工中
						project_status = "04" ;
						project_status_name = "施工中" ;
					}
					
					if("03".equals(woder_status)) { //如果工单状态为拒单，将工单状态改为待分配
						project_status = "02" ;
						project_status_name = "待分配" ;
					}
					
					if("06".equals(woder_status)) { //如果工单状态为拒单，将工单状态改为待分配
						project_status = "06" ;
						project_status_name = "待验收" ;
					}
					
					if(!"00".equals(project_status)) {
						LinkedHashMap<String, Object> e = new LinkedHashMap<String, Object>();
						e.put("tableName", "t_work_order_info");
						e.put("keys", "work_order_id");
						e.put("work_order_id", param.get("work_order_id"));
						PageData resTmp = getDetailsByKey(e) ;
						if(resTmp.getRetCode()!=0) {
							rollback_newg(newg_status);
							return resTmp ;
						}
						
						updateProjectStatus((String)resTmp.getData().get("dd_id"),project_status) ;
						//插入项目记录表
						e = new LinkedHashMap<String, Object>();
						e.put("tableName", "t_project_record");
						e.put("project_uuid", (String)resTmp.getData().get("dd_id"));
						e.put("project_status", project_status);
						e.put("remark", project_status_name);
						
						DataReq reqLog = new DataReq() ;
						reqLog.setReqData(e);
						reqLog.setUinfo(req.getUinfo());
						resTmp = insertTableData(newg_status, reqLog) ;
						if(resTmp.getRetCode()!=0) {
							rollback_newg(newg_status);
							return resTmp ;
						}
					}
					
					//工单完成，插入设备信息
					if ("06".equals(woder_status)) {
						res = addOpeDev(req) ;
						if(res.getRetCode()!=0) {
							rollback_newg(newg_status);
							return res ;
						}
						
					}
					
					commit_newg(newg_status);
					res.setRetCode(0);
					res.setRetMessage("OK");
				}
			}
		} catch (Exception e) {
			rollback_newg(newg_status);
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	
	public PageData getWorkOrderList(DataReq req) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		StringBuffer sbfrom = new StringBuffer();
		try {
			//施工中项目列表
			sb.delete(0, sb.length()) ;
			sb.append("SELECT '1' style,	a.images,d.`status` workorder_status_code ,d.auditStatus,d.auditComments,d.auditUserId,u1.nickName auditUserName, date_format(d.auditTime,'%Y-%m-%d %T') auditTime,a.id,a.project_address,d.work_order_id,a.uuid,a.project_id project_no,a.charge,a.id project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(d.createTime,'%Y-%m-%d %T') createTime,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T') planned_end_time,date_format(c.start_time,'%Y-%m-%d %T') start_time,date_format(c.end_time,'%Y-%m-%d %T') end_time,b.name project_status_name,m.name project_type_name,n.name service_mode_name,x.name project_level_name,date_format(d.createTime,'%Y-%m-%d %T')  workorder_time,f.NAME workorder_status,g.nickName maintain_name,g.phone userphone,g.longitude user_longitude,g.latitude user_latitude,if(d.contact is null ,a.contact,d.contact) contact,if(d.contact_phone is null ,a.contact_phone,d.contact_phone) contact_phone,a.investment_scale,a.uuid project_uuid,a.area_code, REPLACE(xzqh.xzqhqc,'->','') xzqhqc  from 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90'  ") ;
			sb.append(" INNER JOIN t_param m on a.project_type=m.`value` and m.param_type='92'  ") ;
			sb.append(" INNER JOIN t_param n on a.service_mode=n.`value` and n.param_type='91'  ") ;
			sb.append(" INNER JOIN t_param x on a.project_level=x.`value` and x.param_type='93'  ") ;
			sb.append(" LEFT JOIN t_work_order_info  d ON d.dd_id = a.uuid  ") ;
			sb.append(" LEFT JOIN t_param f on  d.`status` = f.`value`  AND f.param_type = '94'  ") ;
			sb.append(" LEFT JOIN t_user g on  g.id = d.maintain_id  ") ;
			sb.append(" LEFT JOIN t_project_scheduling c on a.uuid=c.project_uuid ");
			sb.append(" LEFT JOIN t_user u1 on  u1.id = d.auditUserId  ") ;
			sb.append(" LEFT JOIN t_xzqh xzqh on  xzqh.xzqhdm = a.area_code  ") ;
			sb.append(" where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  ");
			if("01".equals(req.getUinfo().getUserType())) { //如果是安装人员，那么看自己的单据
				sb.append("  and (d.maintain_id =").append(req.getUinfo().getId()).append(" or a.createUid=").append(req.getUinfo().getId()).append(")" );
			}
			
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			
			if(!StringUtils.isEmpty((String)req.getReqData().get("project_uuid"))) {
				sb.append("  and a.uuid =  '").append(req.getReqData().get("project_uuid")).append("' ");
			}
			
			//安装人员
			if(!StringUtils.isEmpty((String)req.getReqData().get("maintain_id"))) {
				sb.append("  and d.maintain_id =  '").append(req.getReqData().get("maintain_id")).append("' ");
			}
			
			
			
			//进行中的工单
			if(!StringUtils.isEmpty((String) req.getReqData().get("in_progress"))) {
				sb.append("  and d.`status` <  '06' and d.`status` >  '01' ");  // 已分配未完成的工单
				
			} 
			
			
			//审核状态
			if(!StringUtils.isEmpty((String) req.getReqData().get("auditStatus"))) {
				sb.append("  and d.auditStatus =  '").append(req.getReqData().get("auditStatus")).append("' ");
				sb.append("  and (a.project_status =  '06' or a.project_status =  '07')");
			} 
			
			
			
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			
			//项目名称
			if(!StringUtils.isEmpty((String)req.getReqData().get("project_name"))) {
				sb.append("  and a.project_name like  '%").append(req.getReqData().get("project_name")).append("%' ");
			}
			
			//项目类型
			if(!StringUtils.isEmpty((String)req.getReqData().get("project_type"))) {
				sb.append("  and a.project_type =  '").append(req.getReqData().get("project_type")).append("' ");
			}
			
			
			//时间
			if(!StringUtils.isEmpty((String)req.getReqData().get("start_date"))) {
				sb.append("  and d.createTime >=  '").append(req.getReqData().get("start_date")).append("'");
			}
			if(!StringUtils.isEmpty((String)req.getReqData().get("end_date"))) {
				sb.append("  and d.createTime <=  '").append(req.getReqData().get("end_date")).append(" 23:59:59' ");
			}
			
			//项目状态
			if(!StringUtils.isEmpty((String)req.getReqData().get("project_status"))) {
				sb.append("  and a.project_status =  '").append(req.getReqData().get("project_status")).append("' ");
			}
			
			//项目状态
			if(!StringUtils.isEmpty((String)req.getReqData().get("workorder_status"))) {
				sb.append("  and d.status =  '").append(req.getReqData().get("workorder_status")).append("' ");
			}
			
			//项目状态
			if(!StringUtils.isEmpty((String)req.getReqData().get("department_id"))) {
				sb.append("  and c.department_id =  '").append(req.getReqData().get("department_id")).append("' ");
			}
			
			sbfrom.append(sb.toString());
			int idx = sbfrom.indexOf(" from ");
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(), req.getPageid(), req.getPagesize(), res,new Object[] { req.getUinfo().getCompanyId() });
			
			
			sb.append(" order by  a.createTime desc ");
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			for(int i=0;i<list.size();i++) {
				BigDecimal latitude = (BigDecimal) list.get(i).get("latitude") ;
				BigDecimal longitude = (BigDecimal) list.get(i).get("longitude") ;
				BigDecimal user_longitude = (BigDecimal) list.get(i).get("user_longitude") ;
				BigDecimal user_latitud = (BigDecimal) list.get(i).get("user_latitude") ;
					
				List<BigDecimal>  lnglat = new ArrayList<BigDecimal>();
				lnglat.add(longitude) ;
				lnglat.add(latitude) ;
				list.get(i).put("lnglat", lnglat);
				
				lnglat = new ArrayList<BigDecimal>();
				lnglat.add(user_longitude) ;
				lnglat.add(user_latitud) ;
				list.get(i).put("user_lnglat", lnglat);
				
				String images = (String)list.get(i).get("images") ;
				if(!StringUtils.isEmpty(images)) {
					String[] imageList = images.split(";",-1) ;
					List<String> imgURL = new ArrayList<String>() ;
					for(int k=0;k<imageList.length;k++) {
						String str = imageList[k] ;
						imgURL.add(OssUtil.getImgSignURL(str, 0)) ;
					}
					list.get(i).put("imageList", imgURL) ;
				}
			}
			res.setPage(list);	
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	
	public PageData getWorkOrderRecordList(DataReq req) {
		PageData res = new PageData();
		try {
			String work_order_id = (String) req.getReqData().get("work_order_id") ;
			List<Map<String, Object>> list = getWorkOrderRecordList(work_order_id);
			res.setPage(list);	
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	
	public List<Map<String, Object>> getWorkOrderRecordList(String  work_order_id) {
		StringBuffer sb = new StringBuffer();
		List<Map<String, Object>> list = null ;
		try {
			
			sb.append(" SELECT a.work_order_id,a.record_id,`status`,b.`name` status_name,date_format(a.createTime,'%Y-%m-%d %T') createTime,a.createUid,c.nickName,a.images from t_work_order_record a INNER JOIN t_param b on a.`status`=b.`value` and b.param_type='94' LEFT JOIN t_user c on a.createUid=c.id ") ;
			sb.append(" where a.work_order_id=? ") ;
			sb.append(" ORDER BY a.createTime desc ") ;
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {work_order_id });
			
			for(int i=0;i<list.size();i++) {
				String images = (String)list.get(i).get("images") ;
				if(!StringUtils.isEmpty(images)) {
					String[] imageList = images.split(";",-1) ;
					List<String> imgURL = new ArrayList<String>() ;
					for(int k=0;k<imageList.length;k++) {
						String str = imageList[k] ;
						imgURL.add(OssUtil.getImgSignURL(str, 0)) ;
						
					}
					list.get(i).put("imageList", imgURL) ;
				}
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return list;
	}
	
	public PageData addOpeDev(DataReq req) throws SQLException {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		Connection conn = null ;
		
		try {
			conn = DBUtils.getDBConn_Ope();
			String wx_id=(String)param.get("wx_id");
			String sblx_id=(String)param.get("sblx_id");
			String sbxlh=(String)param.get("sbxlh");
			String zbrq=(String)param.get("zbrq");
			String project_uuid=(String)param.get("project_uuid");
			if(StringUtils.isEmpty(project_uuid)) {
				res.setRetCode(9);
				res.setRetMessage("项目uuid不能为空！");
				return res ;
			}
			
			if(StringUtils.isEmpty(wx_id)) {
				res.setRetCode(9);
				res.setRetMessage("项目传输模块编号不能为空！");
				return res ;
			}
			
			if(StringUtils.isEmpty(sblx_id)) {
				res.setRetCode(9);
				res.setRetMessage("项目型号ID不能为空！");
				return res ;
			}
			
			if(StringUtils.isEmpty(sbxlh)) {
				res.setRetCode(9);
				res.setRetMessage("设备序列号不能为空！");
				return res ;
			}
			
			if(StringUtils.isEmpty(zbrq)) {
				res.setRetCode(9);
				res.setRetMessage("设备质保日期不能为空！");
				return res ;
			}
			
			
			String jd=(String)param.get("longitude");
			String wd=(String)param.get("latitude");
			String xmlxr=(String)param.get("contact");
			String xmlxrdh=(String)param.get("contact_phone");
			String xmdz=(String)param.get("project_address");
			String area_code=(String)param.get("area_code");
			
			String manufacture_id_ope = (String)param.get("manufacture_id");
			
			
			//根据工单编号获取项目信息
			PageData resTmp = getProjectInfo(project_uuid) ;
			if(resTmp.getRetCode()==0) {
				if(StringUtils.isEmpty(xmlxr)) {
					xmlxr = (String) resTmp.getData().get("contact") ;
				}
				if(StringUtils.isEmpty(xmlxrdh)) {
					xmlxrdh = (String) resTmp.getData().get("contact_phone") ;
				}
				if(StringUtils.isEmpty(xmdz)) {
					xmdz = (String) resTmp.getData().get("project_address") ;
				}
				if(StringUtils.isEmpty(area_code)) {
					area_code = (String) resTmp.getData().get("area_code") ;
				}
				if(StringUtils.isEmpty(jd)) {
					jd = (String) resTmp.getData().get("longitude") ;
				}
				if(StringUtils.isEmpty(wd)) {
					wd = (String) resTmp.getData().get("latitude") ;
				}
			}
			
			
			if(StringUtils.isEmpty(xmlxr)) {
				res.setRetCode(9);
				res.setRetMessage("项目联系人不能为空！");
				return res ;
			}
			
			if(StringUtils.isEmpty(xmlxrdh)) {
				res.setRetCode(9);
				res.setRetMessage("项目联系人电话不能为空！");
				return res ;
			}
			
			if(StringUtils.isEmpty(xmdz)) {
				res.setRetCode(9);
				res.setRetMessage("项目地址不能为空！");
				return res ;
			}
			
			if(StringUtils.isEmpty(area_code)) {
				res.setRetCode(9);
				res.setRetMessage("项目所属区域不能为空！");
				return res ;
			}
			
			if(StringUtils.isEmpty(jd)) {
				res.setRetCode(9);
				res.setRetMessage("项目经度不能为空！");
				return res ;
			}
			
			if(StringUtils.isEmpty(wd)) {
				res.setRetCode(9);
				res.setRetMessage("项目纬度不能为空！");
				return res ;
			}
			
			
			String sfdls= "0" ;
			String agent_id="";
			if("02".equals(req.getUinfo().getCompanyType())) {
				sfdls= "1" ;
				agent_id = req.getUinfo().getCompanyId() ;
			}
			
			
			//根据wxID查询厂商ID，协议ID
			resTmp = getWxInfo(conn,wx_id,manufacture_id_ope) ;
			if(resTmp.getRetCode()!=0) {
				return resTmp ;
			}
			
			
			String manufacture_id = (String) resTmp.getData().get("manufacture_id") ;
			String protocol_id = (String) resTmp.getData().get("protocol_id") ;
			
			//生成客户
			Map<String, String> e = new LinkedHashMap<String, String>() ;
			String kh_id = DBUtils.getDjbh(req.getUinfo().getCompanyId(), "KH_") ;
			e.put("kh_id", kh_id) ;
			e.put("manufacture_id", manufacture_id) ; //待修改
			e.put("khmc", xmlxr) ;
			e.put("khjc", xmlxr) ;
			e.put("khlxr", xmlxr) ;
			e.put("lxdh", xmlxrdh) ;
			e.put("ssqy", area_code) ;
			e.put("sfdls", sfdls) ;
			e.put("agent_id", agent_id) ;
			e.put("sjly", "agent") ;
			int cnt = insertData(conn,"is_client_info",e) ;
			if(cnt<=0) {
				conn.rollback();
				res.setRetCode(9);
				res.setRetMessage("is_client_info插入失败");
				return res ;
			}
			
			//生成合同
			e = new LinkedHashMap<String, String>() ;
			String htbh_id = DBUtils.getDjbh(req.getUinfo().getCompanyId(), "HT_") ;
			e.put("htbh_id", htbh_id) ;
			e.put("manufacture_id", manufacture_id) ; //待修改
			e.put("htlx", "htlx-01") ;
			e.put("htbh", htbh_id) ;
			e.put("xsy_id", "000") ;
			e.put("dhf_id", kh_id) ;
			e.put("xssl", "1") ;
			e.put("qdrq", DBUtils.getCurTime()) ;
			cnt = insertData(conn,"is_contract_info",e) ;
			if(cnt<=0) {
				conn.rollback();
				res.setRetCode(9);
				res.setRetMessage("is_contract_info插入失败");
				return res ;
			}
			
			//生成Install_Info
			e = new LinkedHashMap<String, String>() ;
			String dd_id = project_uuid ; //DBUtils.getDjbh(req.getUinfo().getCompanyId(), "DD_") ;
			e.put("dd_id", dd_id) ;
			e.put("manufacture_id", manufacture_id) ;
			e.put("kh_id", kh_id) ;
			e.put("htbh", htbh_id) ;
			e.put("xmdz", xmdz) ;
			e.put("xmlxr", xmlxr) ;
			e.put("xmlxrdh", xmlxrdh) ;
			e.put("xzqh", area_code) ;
			e.put("xmmc", xmdz) ;
			e.put("xmjc", xmdz) ;
			e.put("xmbh", "000") ;
			e.put("jd", jd) ;
			e.put("wd", wd) ;
			e.put("sjly", "agent") ;
			cnt = insertData(conn,"is_install_info",e) ;
			if(cnt<=0) {
				conn.rollback();
				res.setRetCode(9);
				res.setRetMessage("is_install_info插入失败");
				return res ;
			}
			
			//生成设备表
			e = new LinkedHashMap<String, String>() ;
//			String sb_id = DBUtils.getDjbh(req.getUinfo().getCompanyId(), "SB_") ;
			String sb_id = "SB_" + IdUtils.getIncreaseIdByNanoTime();
			e.put("sb_id", sb_id) ;
			e.put("dd_id", dd_id) ;
			e.put("manufacture_id", manufacture_id) ;
			e.put("htbh", htbh_id) ;
			e.put("protocol_id", protocol_id) ;
			e.put("htbh", htbh_id) ;
			e.put("sblx_id", sblx_id) ;
			e.put("sbxlh", sbxlh) ;
			e.put("jd", jd) ;
			e.put("wd", wd) ;
			e.put("sjly", "agent") ;
			e.put("zbq", zbrq) ;
			e.put("wx_id", wx_id) ;
			e.put("sjly", "agent") ;
			cnt = insertData(conn,"is_equipment_info",e) ;
			if(cnt<=0) {
				conn.rollback();
				res.setRetCode(9);
				res.setRetMessage("is_equipment_info插入失败");
				return res ;
			}
			
			conn.commit();
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
			conn.rollback();
		} finally {
			if(conn!=null)
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return res;
	}
	
	public int insertData(Connection conn,String tblname,Map<String, String> e) throws SQLException {
		StringBuffer sb_field = new StringBuffer();
		StringBuffer sb_value = new StringBuffer();
		List<String> listData = new ArrayList<String> ();
		for (Map.Entry<String, String> entry : e.entrySet()) {
			sb_field.append("`").append(entry.getKey()).append("`,") ;
			sb_value.append("?").append(",") ;
			listData.add(entry.getValue()) ;
		 }
		sb_field.deleteCharAt(sb_field.length()-1) ;
		sb_value.deleteCharAt(sb_value.length()-1) ;
		sb_field.insert(0, "(").insert(0, "`"+tblname+"`").insert(0, "insert into ") ;
		sb_field.append(")values(").append(sb_value).append(")") ;
		
		int cnt = update(conn,sb_field.toString(),listData.toArray()) ;
		return cnt ;
	}
	
	
	
   public int  update(Connection conn,String sql,Object[] obj ) throws SQLException  {
	  	 PreparedStatement ps = null;
			 int cnt = 0 ;
			 try {
				 ps = conn.prepareStatement(sql,
							ResultSet.TYPE_SCROLL_INSENSITIVE,
							ResultSet.CONCUR_UPDATABLE);
				if(obj!=null && obj.length>0) {
					for(int i=0;i<obj.length;i++) {
						ps.setObject(i+1, obj[i]);
					}
				}
				cnt = ps.executeUpdate();
			} finally {
				ps.close();
			} 
	  	 return cnt ;
	  }
   
   public List<Map<String, Object>>  queryForList(Connection conn,String sql,Object[] obj ) throws SQLException  {
	   	 List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
	   	 PreparedStatement ps = null;
			 ResultSet rs = null;
			 try {
				ps = conn
						.prepareStatement(sql,
								ResultSet.TYPE_SCROLL_SENSITIVE,
								ResultSet.CONCUR_READ_ONLY);
				if(obj!=null && obj.length>0) {
					for(int i=0;i<obj.length;i++) {
						ps.setObject(i+1, obj[i]);
					}
				}
				rs = ps.executeQuery();
				ResultSetMetaData rsmd = rs.getMetaData();
				int columnCount = rsmd.getColumnCount();
				while (rs.next()) {
					Map<String, Object>  map = new LinkedHashMap<String, Object>();
					for (int k = 1; k <= columnCount; k++) {
						map.put(rsmd.getColumnLabel(k),
								rs.getObject(k)==null?"":rs.getObject(k));
					}
					list.add(map);
				}
			} finally {
				if(rs!=null) rs.close();
				if(rs!=null) ps.close();
			} 
	   	 return list ;
	   }
   

	public PageData getOpportunityList(DataReq req) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		StringBuffer sbfrom = new StringBuffer();
		
		try {
			sb.append(" SELECT a.uuid opportunity_uuid, a.opportunitystage,a.opportunitytype,a.opportunityname,a.customerId customer_uuid,a.clueId clue_uuid,a.opportunityowner,a.xzqhdm,a.projectaddress,a.projectarea,a.longitude,a.latitude,a.personincharge,a.opportunitymoney   from opportunity a  ");
			sb.append(" where  not EXISTS (SELECT 1 from t_project b where a.uuid=b.contract_no) ");
			//sb.append(" and a.opportunitystage = '05' ") ;
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.xzqhdm",new String[] {"a.createUid",req.getUinfo().getId()}));
			
	
			//项目名称
			if(!StringUtils.isEmpty((String)req.getReqData().get("opportunityname"))) {
				sb.append("  and a.opportunityname like  '%").append(req.getReqData().get("opportunityname")).append("%' ");
			}
			
			//时间
			if(!StringUtils.isEmpty((String)req.getReqData().get("start_date"))) {
				sb.append("  and d.createTime >=  '").append(req.getReqData().get("start_date")).append("'");
			}
			if(!StringUtils.isEmpty((String)req.getReqData().get("end_date"))) {
				sb.append("  and d.createTime <=  '").append(req.getReqData().get("end_date")).append(" 23:59:59' ");
			}
			
			sb.append(" order by  a.createTime desc limit 100 ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {});

			res.setPage(list);	
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getProjectCheckList(DataReq req) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		StringBuffer sbfrom = new StringBuffer();
		try {
			
			//施工中项目列表
			sb.delete(0, sb.length()) ;
			sb.append("SELECT 	a.id,a.project_address,a.uuid project_uuid,a.project_id project_no,a.charge,a.project_name,a.project_status,date_format(a.createTime,'%Y-%m-%d %T') createTime,b.name project_status_name,m.name project_type_name,n.name service_mode_name,x.name project_level_name,f.NAME workorder_status,g.nickname maintain_name,pc.check_no,pc.uuid check_uuid,pc.check_type,pc.check_user_id,pc.check_content,pc.check_results,pc.images,pc.check_status,date_format(pc.checkTime,'%Y-%m-%d %T') checkTime  from 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90'  ") ;
			sb.append(" INNER JOIN t_param m on a.project_type=m.`value` and m.param_type='92'  ") ;
			sb.append(" INNER JOIN t_param n on a.service_mode=n.`value` and n.param_type='91'  ") ;
			sb.append(" INNER JOIN t_param x on a.project_level=x.`value` and x.param_type='93'  ") ;
			sb.append(" LEFT JOIN t_work_order_info  d ON d.dd_id = a.uuid  ") ;
			sb.append(" LEFT JOIN t_param f on  d.`status` = f.`value`  AND f.param_type = '94'  ") ;
			sb.append(" INNER JOIN t_project_check pc on  a.uuid = pc.project_uuid  ") ;
			sb.append(" INNER JOIN t_user g on  g.id = pc.check_user_id  ") ;
			sb.append(" LEFT JOIN t_project_scheduling c on a.uuid=c.project_uuid where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  ");

			
			
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			
			
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			
			//检查UUID
			if(!StringUtils.isEmpty((String)req.getReqData().get("uuid"))) {
				sb.append("  and pc.uuid =  '").append(req.getReqData().get("uuid")).append("' ");
			}
			
			
			//项目UUID
			if(!StringUtils.isEmpty((String)req.getReqData().get("project_uuid"))) {
				sb.append("  and a.uuid =  '").append(req.getReqData().get("project_uuid")).append("' ");
			}
			
			//项目名称
			if(!StringUtils.isEmpty((String)req.getReqData().get("project_name"))) {
				sb.append("  and a.project_name like  '%").append(req.getReqData().get("project_name")).append("%' ");
			}
			
			//项目类型
			if(!StringUtils.isEmpty((String)req.getReqData().get("project_type"))) {
				sb.append("  and a.project_type =  '").append(req.getReqData().get("project_type")).append("' ");
			}
			
			
			//时间
			if(!StringUtils.isEmpty((String)req.getReqData().get("start_date"))) {
				sb.append("  and pc.checkTime >=  '").append(req.getReqData().get("start_date")).append("'");
			}
			if(!StringUtils.isEmpty((String)req.getReqData().get("end_date"))) {
				sb.append("  and pc.checkTime <=  '").append(req.getReqData().get("end_date")).append(" 23:59:59' ");
			}
			
			//项目状态
			if(!StringUtils.isEmpty((String)req.getReqData().get("project_status"))) {
				sb.append("  and a.project_status =  '").append(req.getReqData().get("project_status")).append("' ");
			}
			
			//检查状态
			if(!StringUtils.isEmpty((String)req.getReqData().get("check_status"))) {
				sb.append("  and pc.check_status =  '").append(req.getReqData().get("check_status")).append("' ");
			}
			
			//检查类型
			sb.append("  and pc.check_type =  '").append(req.getReqData().get("check_type")).append("' ");
			

			if(!StringUtils.isEmpty((String)req.getReqData().get("department_id"))) {
				sb.append("  and c.department_id =  '").append(req.getReqData().get("department_id")).append("' ");
			}
			
			sbfrom.append(sb.toString());
			int idx = sbfrom.indexOf(" from ");
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(), req.getPageid(), req.getPagesize(), res,new Object[] { req.getUinfo().getCompanyId() });
			
			
			sb.append(" order by  a.createTime desc ");
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			
			
			for(int i=0;i<list.size();i++) {
				String images = (String)list.get(i).get("images") ;
				if(!StringUtils.isEmpty(images)) {
					String[] imageList = images.split(";",-1) ;
					List<String> imgURL = new ArrayList<String>() ;
					for(int k=0;k<imageList.length;k++) {
						String str = imageList[k] ;
						imgURL.add(OssUtil.getImgSignURL(str, 0)) ;
						
					}
					list.get(i).put("imageList", imgURL) ;
				}
			}
			
			res.setPage(list);	
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getLogList(DataReq req) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		StringBuffer sbfrom = new StringBuffer();
		try {
			
			//施工中项目列表
			sb.delete(0, sb.length()) ;

			sb.append(" SELECT ") ;
			sb.append(" date_format( a.createTime, '%Y-%m-%d %T' ) createTime, ") ;
			sb.append(" IF ") ;
			sb.append(" ( a.link_type = '2', d.dd_id, a.link_id ) link_id, ") ;
			sb.append(" a.link_type, ") ;
			sb.append(" x.NAME link_type_name, ") ;
			sb.append(" a.title, ") ;
			sb.append(" b.areacode, ") ;
			sb.append(" b.username, ") ;
			sb.append(" a.remark  ") ;
			sb.append(" from ") ;
			sb.append(" t_project_log a ") ;
			sb.append(" INNER JOIN t_user b ON a.createUid = b.id ") ;
			sb.append(" INNER JOIN t_param x ON a.link_type = x.`value`  ") ;
			sb.append(" AND x.param_type = '84' ") ;
			sb.append(" LEFT JOIN t_project c ON a.link_id = c.uuid  ") ;
			sb.append(" AND a.link_type = '1' ") ;
			sb.append(" LEFT JOIN t_work_order_info d ON a.link_id = d.work_order_id  ") ;
			sb.append(" AND a.link_type = '2' ") ;
			sb.append(" LEFT JOIN t_project_check e ON a.link_id = e.uuid  ") ;
			sb.append(" AND ( a.link_type = '3' OR a.link_type = '4' ) ") ;
			sb.append(" LEFT JOIN t_project_bulid_log f ON a.link_id = f.uuid ") ;

			sb.append("  where a.link_type<>'5' and b.companyId like concat(?,'%') ") ;
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"b.areacode",new String[] {"a.createUid",req.getUinfo().getId()}));
			
			if(!StringUtils.isEmpty((String)req.getReqData().get("link_type"))) {
				sb.append("  and a.link_type  = '").append(req.getReqData().get("link_type")).append("' ");
			}
			
			
			//时间
			if(!StringUtils.isEmpty((String)req.getReqData().get("start_date"))) {
				sb.append("  and a.createTime >=  '").append(req.getReqData().get("start_date")).append("'");
			}
			if(!StringUtils.isEmpty((String)req.getReqData().get("end_date"))) {
				sb.append("  and a.createTime <=  '").append(req.getReqData().get("end_date")).append(" 23:59:59' ");
			}
			
			sbfrom.append(sb.toString());
			int idx = sbfrom.indexOf(" from ");
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(), req.getPageid(), req.getPagesize(), res,new Object[] { req.getUinfo().getCompanyId() });
			
			
			sb.append(" order by  a.createTime desc ");
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			
			res.setPage(list);	
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	
	public PageData getVisualization(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		Map<String,Object> extData = new LinkedHashMap<String, Object>();
		try {
			//获取我的代办事项
			sb.delete(0, sb.length()) ;
			sb.append(" select b.`value` ,b.`name`,count(a.project_status) cnt from t_param b LEFT JOIN t_project a on a.project_status=b.`value` and (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  ") ;
			
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			
		
					
					
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			
			sb.append(" where b.param_type='90'  ") ;
					 
			sb.append(" GROUP BY b.`value` order BY b.`value` ") ;
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			//加总数
			BigDecimal sum = new BigDecimal("0") ;
			for(int i=0;i<list.size();i++) {
				String cnt = list.get(i).get("cnt").toString() ;
				sum = sum.add(new BigDecimal(cnt)) ;
			}
			list.get(0).put("sumcnt", sum.toString()) ;
			extData.put("topData", list) ;
			
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT") ;
			sb.append(" sum(if(b.`status`='01',1,0)) plan_cnt,sum(if(b.`status`='02',1,0)) dispatch_cnt from t_work_order_info a INNER JOIN t_work_order_record b on a.work_order_id=b.work_order_id ") ;
			sb.append(" where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("') and b.createTime>=date(now()) and (b.`status`='01' or b.`status`='02')") ;
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.xzqhdm",new String[] {"a.createUid",req.getUinfo().getId()}));
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			Map<String,Object> e = new LinkedHashMap<String, Object>();
			if(list.size()>0) {
				
				e.put("plan_cnt", list.get(0).get("plan_cnt")) ;
				e.put("dispatch_cnt", list.get(0).get("dispatch_cnt")) ;
			}else {
				e.put("plan_cnt", "0") ;
				e.put("dispatch_cnt", "0") ;
			}
			extData.put("topData_today", e) ;
	
			//工单状态数据
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT") ;
			sb.append(" sum( IF ( a.`status` = '01', 1, 0 ) ) status01,") ;
			sb.append(" sum( IF ( a.`status` = '02', 1, 0 ) ) status02, ") ;
			sb.append(" sum( IF ( a.`status` = '05', 1, 0 ) ) status05, ") ;
			sb.append(" sum( IF ( a.`status` = '06' or a.`status` = '07' or a.`status` = '08', 1, 0 ) ) status06") ;
			sb.append(" FROM") ;
			sb.append(" t_work_order_info a where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("') ") ;
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.xzqhdm",new String[] {"a.createUid",req.getUinfo().getId()}));
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			e = new LinkedHashMap<String, Object>();
			if(list.size()>0) {
				e.put("status01", list.get(0).get("status01")) ;
				e.put("status02", list.get(0).get("status02")) ;
				e.put("status05", list.get(0).get("status05")) ;
				e.put("status06", list.get(0).get("status06")) ;
			}else {
				e.put("status01", "0") ;
				e.put("status02", "0") ;
				e.put("status05", "0") ;
				e.put("status06", "0") ;
			}
			extData.put("workorder_status", e) ;
			
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT ");
			sb.append("  date_format(b.createTime,'%Y-%m-%d') createTime,sum(if(a.project_type='01',1,0)) civil_cnt,sum(if(a.project_type='02',1,0)) Industry_cnt ");
			sb.append(" FROM t_project a INNER JOIN  t_work_order_info b on a.uuid=b.dd_id ");
			sb.append("   where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  and a.createTime>'").append(DBUtils.getDate(-10)).append("' ") ;
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			sb.append(" GROUP BY date_format(b.createTime,'%Y-%m-%d') ");
			sb.append(" order BY date_format(b.createTime,'%Y-%m-%d') ");
			
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("barData", list) ;
			
			//项目数
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT ");
			sb.append("   count(1) all_cnt,year(now())-2 year01_name,year(now())-1 year02_name,year(now()) year03_name, ");
			sb.append(" 	sum( IF(year(a.createTime) = year(now())-2, 1, 0 )) year01 , ");
			sb.append(" sum( IF(year(a.createTime) = year(now())-1, 1, 0 )) year02 , ");
			sb.append(" 	sum( IF(year(a.createTime) = year(now()), 1, 0 ) ) year03 ,sum( IF(b.end_time<now() and a.project_status<>'07', 1, 0 ) ) later_cnt  ");
			sb.append(" FROM ");
			sb.append(" 	t_project a ");
			sb.append(" 	LEFT JOIN t_project_scheduling b ON a.uuid = b.project_uuid   ");
			sb.append("   where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  and a.createTime>'").append(DBUtils.getDate(-10)).append("' ") ;
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("projectData", list) ;
			
			
			res.setExtData(extData);		
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getAgentManufactorList(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		Map<String,Object> extData = new LinkedHashMap<String, Object>();
		try {
			//获取我的代办事项
			sb.delete(0, sb.length()) ;
			sb.append(" select b.`value` ,b.`name`,count(a.project_status) cnt from t_param b LEFT JOIN t_project a on a.project_status=b.`value` and (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')   ") ;
			
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			
					
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			
			sb.append(" where b.param_type='90'  ") ;
					 
			sb.append(" GROUP BY b.`value` order BY b.`value` ") ;
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			//加总数
			BigDecimal sum = new BigDecimal("0") ;
			for(int i=0;i<list.size();i++) {
				String cnt = list.get(i).get("cnt").toString() ;
				sum = sum.add(new BigDecimal(cnt)) ;
			}
			list.get(0).put("sumcnt", sum.toString()) ;
			extData.put("topData", list) ;
			
			
			
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT") ;
			sb.append(" sum(if(b.`status`='01',1,0)) plan_cnt,sum(if(b.`status`='02',1,0)) dispatch_cnt from t_work_order_info a INNER JOIN t_work_order_record b on a.work_order_id=b.work_order_id ") ;
			sb.append(" where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("') and b.createTime>=date(now()) and (b.`status`='01' or b.`status`='02')") ;
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.xzqhdm",new String[] {"a.createUid",req.getUinfo().getId()}));
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			Map<String,Object> e = new LinkedHashMap<String, Object>();
			if(list.size()>0) {
				
				e.put("plan_cnt", list.get(0).get("plan_cnt")) ;
				e.put("dispatch_cnt", list.get(0).get("dispatch_cnt")) ;
			}else {
				e.put("plan_cnt", "0") ;
				e.put("dispatch_cnt", "0") ;
			}
			extData.put("topData_today", e) ;
	
			
			//工单状态数据
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT") ;
			sb.append(" sum( IF ( a.`status` = '01', 1, 0 ) ) status01,") ;
			sb.append(" sum( IF ( a.`status` = '02', 1, 0 ) ) status02, ") ;
			sb.append(" sum( IF ( a.`status` = '05', 1, 0 ) ) status05, ") ;
			sb.append(" sum( IF ( a.`status` = '06' or a.`status` = '07' or a.`status` = '08', 1, 0 ) ) status06") ;
			sb.append(" FROM") ;
			sb.append(" t_work_order_info a where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("') ") ;
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.xzqhdm",new String[] {"a.createUid",req.getUinfo().getId()}));
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			e = new LinkedHashMap<String, Object>();
			if(list.size()>0) {
				e.put("status01", list.get(0).get("status01")) ;
				e.put("status02", list.get(0).get("status02")) ;
				e.put("status05", list.get(0).get("status05")) ;
				e.put("status06", list.get(0).get("status06")) ;
			}else {
				e.put("status01", "0") ;
				e.put("status02", "0") ;
				e.put("status05", "0") ;
				e.put("status06", "0") ;
			}
			extData.put("workorder_status", e) ;
			
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT ");
			sb.append("  date_format(b.createTime,'%Y-%m-%d') createTime,sum(if(a.project_type='01',1,0)) civil_cnt,sum(if(a.project_type='02',1,0)) Industry_cnt ");
			sb.append(" FROM t_project a INNER JOIN  t_work_order_info b on a.uuid=b.dd_id ");
			sb.append("   where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  and a.createTime>'").append(DBUtils.getDate(-10)).append("' ") ;
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			sb.append(" GROUP BY date_format(b.createTime,'%Y-%m-%d') ");
			sb.append(" order BY date_format(b.createTime,'%Y-%m-%d') ");
			
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("barData", list) ;
			
			//项目数
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT ");
			sb.append("   count(1) all_cnt,year(now())-2 year01_name,year(now())-1 year02_name,year(now()) year03_name, ");
			sb.append(" 	sum( IF(year(a.createTime) = year(now())-2, 1, 0 )) year01 , ");
			sb.append(" sum( IF(year(a.createTime) = year(now())-1, 1, 0 )) year02 , ");
			sb.append(" 	sum( IF(year(a.createTime) = year(now()), 1, 0 ) ) year03 ,sum( IF(b.end_time<now() and a.project_status<>'07', 1, 0 ) ) later_cnt  ");
			sb.append(" FROM ");
			sb.append(" 	t_project a ");
			sb.append(" 	INNER JOIN t_project_scheduling b ON a.uuid = b.project_uuid   ");
			sb.append("   where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  and a.createTime>'").append(DBUtils.getDate(-10)).append("' ") ;
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("projectData", list) ;
			
			
			res.setExtData(extData);		
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getProjectfileList(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        try {
            StringBuffer sb = new StringBuffer();
            StringBuffer sbfrom = new StringBuffer() ;
            //获取文件列表
            sb.append(" select d.username,date_format(a.createtime,'%Y-%m-%d %H:%i:%S') createtime,a.uuid,a.projectId,a.filetype,c.`name` filetypeName,a.filename,a.filepath,a.memo " );
            sb.append(" from project_file a " );
            sb.append(" left join t_param c on a.filetype=c.`value` and c.param_type='29' ");
            sb.append(" left join t_user d on a.createUid=d.id ");
            sb.append(" where a.isdelete=0 and projectId=? " );
            if (!StringUtils.isEmpty((String)param.get("fileName"))){
				sb.append(" and a.filename like '%").append(param.get("fileName")).append("%' ") ;
			}
            if (!StringUtils.isEmpty((String)param.get("fileType"))){
				sb.append(" and a.filetype = '").append(param.get("fileType")).append("' ") ;
			}
            sb.append(" order by a.createTime desc");
            
            sb = DBUtils.sql_check(sb) ;
			sbfrom.append(sb.toString()) ;
			int idx=sbfrom.indexOf(" from ") ;
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{param.get("projectId")}) ;
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;			
			List<Map<String, Object>> fileList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("projectId")});
            
			//获取客户文件真实路径
            Iterator<Map<String, Object>> iterator = fileList.iterator();
            while(iterator.hasNext()){
            	Map<String, Object> next = iterator.next();
            	String filepath = (String) next.get("filepath");
            	String imgSignURL = OssUtil.getFileSignURL(filepath, 0);
            	next.put("realFilePath", imgSignURL);
            }
			
			res.setPage(fileList);

            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }
	
	public PageData saveProjectFile(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "project_file");
		param.put("isdelete", "0");
		//添加操作日志
		param.put("OperationLogType", "02");
		param.put("OperationLogId", "updateCustomerId");
		try {
			
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				param.put("OperationLogName", "增加项目文件");
				param.put("OperationMemo", req.getUinfo().getNickName()+"添加了一条项目文件");
				res =  insertTableData(newg_status, req) ;
			}else {
				param.put("keys", "uuid");
				param.put("OperationLogName", "修改项目文件");
				param.put("OperationMemo", req.getUinfo().getNickName()+"修改了一条项目文件");
				res =  updateTableData(newg_status, req) ;
			}
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
				res.setKey((String)param.get("uuid")) ;
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("数据错误");
			}
		} catch (Exception e) {
			rollback_newg(newg_status);
			e.printStackTrace();
			
		} finally {
			
		}
		return res;
	}
	
	@SuppressWarnings("unchecked")
    public PageData delProjectFile(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("项目文件删除失败");
        newg_status = begin_newg();// 开启事物
        param.put("tableName", "project_file") ;
        param.put("keys", "uuid") ;
        param.put("isdelete", "1");
        param.put("uuid", param.get("fileId"));
        try {
            PageData sqlInfo = getUpdateSQL(req);
            if (sqlInfo.getRetCode() != 0) {
            	rollback_newg(newg_status);
                return sqlInfo;
           }
            //修改主表
            StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());

            param.put("OperationMemo", req.getUinfo().getNickName()+"删除了一条项目文件");
            SaveOperatelog(req,"02","删除项目文件信息",sb_field+"---"+listData,newg_status);
            commit_newg(newg_status);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
        	rollback_newg(newg_status);
            e.printStackTrace();
        } finally {
        	
        }
        return res;
    }
	
	public PageData uploadFile(DataReq req,MultipartFile file) {
		PageData res = new PageData();
		Map<String, Object> param = req.getReqData();
		res.setRetCode(9);
		res.setRetMessage("上传文件失败");
		String fileName="";
		String OriginalFilename="";
		try {
			if (file != null && file.getSize() > 0) {
				String prefix = ".doc";
				OriginalFilename=file.getOriginalFilename();
				if ( OriginalFilename != null) {
					prefix = OriginalFilename.substring(
							OriginalFilename.lastIndexOf("."));
				}
				fileName= "file/" + DBUitl.getDate(0) + "/file_"
						+ param.get("uuid") + "_"
						+ String.valueOf((new java.util.Date()).getTime())
						+ prefix;
				boolean isOK = DBUitl.SaveImageFromStream(
						file.getInputStream() , fileName);
				Map<String, Object> map=new HashMap<String, Object>();
				map.put("filePath", fileName);
				map.put("fileName", OriginalFilename);
				if (!isOK) {
					return res ;
				}else{
					res.setRetCode(0);
					res.setData(map);
					res.setRetMessage("OK");
				}
			}else{
				res.setRetCode(9);
				res.setRetMessage("上传文件失败");
			}

		} catch (Exception e) {
			e.printStackTrace();
			res.setRetCode(9);
			res.setRetMessage("上传文件失败");
		} finally {

		}
		return res;
	}
	
	public PageData sendProjectOvertimeRemind(DataReq req) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		
		try {
			String project_uuid = (String) req.getReqData().get("project_uuid") ;
			
			sb.append(" SELECT  ") ;
			sb.append(" creater.phone creater_phone,  ") ;
			sb.append(" workorder.phone workorder_phone,  ") ;
			sb.append(" scheduling.phone scheduling_phone,a.project_name, date_format(b.end_time,'%Y-%m-%d %T') end_time ") ;
			sb.append(" FROM  ") ;
			sb.append(" t_project a  ") ;
			sb.append(" INNER JOIN t_project_scheduling b ON a.uuid = b.project_uuid  ") ;
			sb.append(" INNER JOIN t_user creater ON creater.id = a.createUid  ") ;
			sb.append(" INNER JOIN t_user scheduling ON scheduling.id = b.createUid  ") ;
			sb.append(" LEFT JOIN t_work_order_info c ON a.uuid = c.dd_id  ") ;
			sb.append(" LEFT JOIN t_user workorder ON workorder.id = c.maintain_id  ") ;
			sb.append("  where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  and b.end_time<now() and a.project_status<>'07' ") ;
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
			if(!StringUtils.isEmpty(project_uuid)) {
				sb.append("  and a.uuid  = '").append(project_uuid).append("' ");
			}
			
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			
			//循环发送短信提醒
			for(int i=0;i<list.size();i++) {
				String creater_phone = (String)list.get(i).get("creater_phone") ;
				String workorder_phone = (String)list.get(i).get("workorder_phone") ;
				String scheduling_phone = (String)list.get(i).get("scheduling_phone") ;
				String projectName = (String)list.get(i).get("project_name") ;
				String time = (String)list.get(i).get("end_time") ;
				if(!StringUtils.isEmpty(creater_phone)) {
					SMSUtil.sendProjectOvertimeRemind(creater_phone, projectName, time);
				}
				if(!StringUtils.isEmpty(workorder_phone)) {
					SMSUtil.sendProjectOvertimeRemind(workorder_phone, projectName, time);
				}
				if(!StringUtils.isEmpty(scheduling_phone)) {
					SMSUtil.sendProjectOvertimeRemind(scheduling_phone, projectName, time);
				}
			}
			
			//res.setPage(list);	
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getWxInfo(Connection conn,String wx_id,String manufacture_id_ope) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		try {
			//发送项目超时提醒
			res.setRetCode(9);
			res.setRetMessage("没有查询到该无线设备信息");
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT a.protocol_id,a.manufacture_id,if(b.sb_id is null,'0','1') isBind from is_wxsb a LEFT JOIN is_equipment_info b on a.wx_id=b.wx_id where a.wx_id=? ");
			List<Map<String, Object>> list = this.queryForList(conn,sb.toString(),
							new Object[] {wx_id});
			if(list.size()==1) {
				String isBind =  (String)list.get(0).get("isBind") ;
				String manufacture_id =  (String)list.get(0).get("manufacture_id") ;
				if("1".equals(isBind)) {
					res.setRetCode(9);
					res.setRetMessage("该无线模块已经绑定");
					return res ;
				 }
				
				if(!manufacture_id.equals(manufacture_id_ope)) {
					res.setRetCode(9);
					res.setRetMessage("该无线模块不属于当前设备公私");
					return res ;
				 }
				
				res.setData(list.get(0));
				res.setRetCode(0);
				res.setRetMessage("OK");
				
			}
			
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getProjectInfo(String uuid) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		try {
			//发送项目超时提醒
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT * from t_project where uuid=? ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(),
							new Object[] {uuid});
			if(list.size()==1) {
				res.setData(list.get(0));
				res.setRetCode(0);
				res.setRetMessage("OK");
			}
			
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData addBulidLog(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "t_project_bulid_log");
		try {
			String logNo=(String)param.get("logNo");
			if(StringUtils.isEmpty(logNo)) {
				logNo = DBUtils.getDjbh(req.getUinfo().getCompanyId(), "LOG") ;
				param.put("logNo", logNo) ;
			}
			
			param.put("company_id", req.getUinfo().getCompanyId()) ;
			
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				res =  insertTableData(newg_status, req) ;
				//插入项目记录表
				if(res.getRetCode()!=0) {
					rollback_newg(newg_status);
					return res ;
				}			
				//插入日志
				String memo = "记录施工日志" ;
				insertProjectLog(newg_status,req,res.getKey(),"5",memo,"记录了"+memo) ;
	
			}else {
				param.put("keys", "uuid");
				res =  updateTableData(newg_status, req) ;
			}
			
			if(res.getRetCode()==0) {
				
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("操作失败！");
			}
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	
	public PageData getBulidList(DataReq req) {
		StringBuffer sb = new StringBuffer();
		List<Map<String, Object>> list = null ;
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		try {
			String project_uuid = (String)param.get("project_uuid") ;
			sb.append(" SELECT b.uuid,a.project_name,b.logNo,b.personnel,b.personnelnum,b.remark,b.images,b.finishRate,date_format(b.createTime,'%Y-%m-%d %T') createTime,a.createUid,c.nickName,b.weather from t_project a INNER JOIN t_project_bulid_log b on a.uuid=b.project_uuid LEFT JOIN t_user c on a.createUid=c.id where a.uuid = ? ") ;
			sb.append(" ORDER BY b.createTime desc ") ;
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {project_uuid });
			
			for(int i=0;i<list.size();i++) {
				String images = (String)list.get(i).get("images") ;
				if(!StringUtils.isEmpty(images)) {
					String[] imageList = images.split(";",-1) ;
					List<String> imgURL = new ArrayList<String>() ;
					for(int k=0;k<imageList.length;k++) {
						String str = imageList[k] ;
						imgURL.add(OssUtil.getImgSignURL(str, 0)) ;
						
					}
					list.get(i).put("imageList", imgURL) ;
				}
			}
			res.setRetCode(0);
			res.setRetMessage("OK");
			res.setPage(list);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getWeather(DataReq req) throws SQLException {
		StringBuffer sb = new StringBuffer();
		List<Map<String, Object>> list = null ;
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		Connection conn = null ;
		try {
			conn = DBUtils.getDBConn_Weather();
			String area_code = (String)param.get("area_code") ;
			if(StringUtils.isEmpty(area_code)) {
				res.setRetCode(9);
				res.setRetMessage("请输入查询的区域代码");
				return  res ;
			}
			
			if(!StringUtils.isEmpty(area_code) && area_code.length()>=6) {
				area_code = area_code.substring(0,6) ;
			}
			sb.append(" SELECT CONCAT(a.text_desc,'   风力：',a.windDir,a.windScale,'   温度：',a.temp,'   湿度：',a.humidity) weather,a.temp,a.text_desc,a.windDir,a.windScale,a.humidity from pre_weateher a INNER JOIN pre_location b on a.city_num=b.city_num where b.xzqhdm like concat(?,'%') ORDER BY tdate desc limit 1 ") ;
			list = this.queryForList(conn,sb.toString(),
							new Object[] {area_code });
			if(list.size()==0 && area_code.length()>=4) {
				area_code = area_code.substring(0,4) ;
				list = this.queryForList(conn,sb.toString(),
						new Object[] {area_code });
			}
			if(list.size()==0 && area_code.length()>=2) {
				area_code = area_code.substring(0,2) ;
				list = this.queryForList(conn,sb.toString(),
						new Object[] {area_code });
			}
			res.setRetCode(0);
			res.setRetMessage("OK");
			res.setPage(list);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return res;
	}
	
	public PageData getPreWeatherList(DataReq req) throws SQLException {
		StringBuffer sb = new StringBuffer();
		List<Map<String, Object>> list = null ;
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		Connection conn = null ;
		try {
			conn = DBUtils.getDBConn_Weather();
			sb.append(" SELECT date_format(a.tdate,'%Y-%m-%d %T') createTime ,a.temp,a.text_desc,a.windDir,concat(a.windScale,'级') windScale,a.humidity,a.pop from pre_weateher a INNER JOIN pre_location b on a.city_num=b.city_num where b.xzqhdm = '110114000000' ORDER BY tdate desc limit 72 ") ;
			list = this.queryForList(conn,sb.toString(),
							new Object[] { });
			res.setRetCode(0);
			res.setRetMessage("OK");
			res.setPage(list);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return res;
	}
	
	//用户签到
	public PageData userSign(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "t_sign");
		try {
			String linkType=(String)param.get("linkType");
			String linkid=(String)param.get("linkid");
			String signType=(String)param.get("signType");
			String remark=(String)param.get("remark");
			if("0".equals(signType)) {
				req.getReqData().put("source_longitude", req.getReqData().get("longitude")) ;
				req.getReqData().put("source_latitude", req.getReqData().get("latitude")) ;
				req.getReqData().put("source_address", req.getReqData().get("address")) ;
				req.getReqData().put("sign_start_time", DBUtils.getCurTime()) ;
			}else {
				req.getReqData().put("dest_longitude", req.getReqData().get("longitude")) ;
				req.getReqData().put("dest_latitude", req.getReqData().get("latitude")) ;
				req.getReqData().put("dest_address", req.getReqData().get("address")) ;
				req.getReqData().put("sign_end_time", DBUtils.getCurTime()) ;
			}
			
			param.put("company_id", req.getUinfo().getCompanyId()) ;
			
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				res =  insertTableData(newg_status, req) ;
				//插入项目记录表
				if(res.getRetCode()!=0) {
					rollback_newg(newg_status);
					return res ;
				}	
			}else {
				param.put("keys", "uuid");
				res =  updateTableData(newg_status, req) ;
			}
			
			
			if(res.getRetCode()==0) {
				
				res.setRetCode(0);
				res.setRetMessage("OK");
			}
			
			if("1".equals(signType)) {//如果是到达，填写拜访内容
				if(StringUtils.isEmpty(linkid)&&!"01".equals(linkType)) {
					rollback_newg(newg_status);
					res.setRetCode(9);
					res.setRetMessage("关联ID不能为空！");
					return res ;
				}
				req.getReqData().put("uuid", null) ;
				req.getReqData().put("remark",remark) ;
				
				switch (linkType) {
				case "01"://地推
					break;
				case "02"://拜访客户
					
					req.getReqData().put("customerId", linkid) ;
					req.getReqData().put("visittype", "02") ;
					req.getReqData().put("visittime", DBUtils.getCurTime()) ;
					req.getReqData().put("visitaddress", req.getReqData().get("address")) ;
					req.getReqData().put("visitcontent", req.getReqData().get("remark")) ;
					System.out.println(req.getReqData().get("visitcontent"));
					res =  customerDaoImpl.saveCustomerClaim(req);
					if(res.getRetCode()!=0) {
						rollback_newg(newg_status);
						return res ;
					}
					break;
				case "03"://施工签到
					String work_order_id = linkid ;
					req.getReqData().put("work_order_id", work_order_id) ;
					req.getReqData().put("status", "05") ;
					req.getReqData().put("describe", "已到达") ;
					req.getReqData().put("maintain_id", req.getUinfo().getId()) ;
					res =  changeOrderStatus(req) ;
					if(res.getRetCode()!=0) {
						rollback_newg(newg_status);
						return res ;
					}	
					break;
				case "04"://售后签到
					
					break;
				default:
					break;
				}
			}
			commit_newg(newg_status);

		} catch (Exception e) {
			rollback_newg(newg_status);
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
			
		} finally {
			
			
		}
		return res;
	}
	
	public PageData getSignList(DataReq req) {
		StringBuffer sb = new StringBuffer();
		StringBuffer condition = new StringBuffer();
		StringBuffer sbfrom = new StringBuffer();
		List<Map<String, Object>> list = null ;
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		try {
			List<Object> pm = new ArrayList<Object>();
			pm.add(req.getUinfo().getCompanyId());
			
			String uuid = (String) req.getReqData().get("uuid") ;
			if(!StringUtils.isEmpty(uuid)) {
				pm.add(uuid) ;
				condition.append(" and a.uuid=? ") ;
			}
			
			sb.append(" SELECT a.uuid,a.signType,a.linkid,a.linktype,a.signName, date_format(a.sign_start_time,'%Y-%m-%d %T') sign_start_time,date_format(a.sign_end_time,'%Y-%m-%d %T')  sign_end_time,a.source_longitude,a.source_latitude,a.dest_longitude,a.dest_latitude,a.dest_address,a.source_address,a.remark,a.tel,a.images,date_format(a.createTime,'%Y-%m-%d %T') createTime,a.createUid,b.name linktypeName from t_sign a INNER JOIN t_param b on a.linkType=b.`value` and b.param_type='103'  ") ;
			sb.append("  where a.company_id like concat(?,'%')   ") ;
			//if("01".equals(req.getUinfo().getUserType())) { //如果是安装人员，那么看自己的单据
				sb.append("  and  a.createUid=").append(req.getUinfo().getId()) ;
			//}
			sb.append(condition) ;
			
			sbfrom.append(sb.toString());
			int idx = sbfrom.indexOf(" from ");
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(), req.getPageid(), req.getPagesize(), res,pm.toArray());
			sb.append(" ORDER BY a.createTime desc ") ;
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							pm.toArray());
			
			if(!StringUtils.isEmpty(uuid)) {
				for(int i=0;i<list.size();i++) {
					String images = (String)list.get(i).get("images") ;
					if(!StringUtils.isEmpty(images)) {
						String[] imageList = images.split(";",-1) ;
						List<String> imgURL = new ArrayList<String>() ;
						for(int k=0;k<imageList.length;k++) {
							String str = imageList[k] ;
							imgURL.add(OssUtil.getImgSignURL(str, 0)) ;
							
						}
						list.get(i).put("imageList", imgURL) ;
					}
					
					String sign_start_time = (String) list.get(i).get("sign_start_time") ;
					String sign_end_time = (String) list.get(i).get("sign_end_time") ;
					list.get(i).put("posList", getPositionList(req.getUinfo().getId(),sign_start_time,sign_end_time)) ;
				}
			}
			
			res.setRetCode(0);
			res.setRetMessage("OK");
			res.setPage(list);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	
	public PageData getSignObjectList(DataReq req) {
		StringBuffer sb = new StringBuffer();
		StringBuffer sbfrom = new StringBuffer();
		List<Map<String, Object>> list = null ;
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		try {
			String linkType=(String)param.get("linkType");
			//默认查询客户
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT uuid ,customername,address,phone, personincharge contact from customer a ") ;
			sb.append("  where a.companyid like concat(?,'%')   ") ;
			sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.xzqhdm",new String[] {"a.createUid",req.getUinfo().getId()}));
			
			
			if("03".equals(linkType)) {//工单
				sb.delete(0, sb.length()) ;
				sb.append("SELECT  b.work_order_id uuid,a.project_name customername,a.project_address address,b.contact_phone phone,b.contact from t_project a INNER JOIN t_work_order_info b on a.uuid=b.dd_id where 1=1 "); // b.`status`='04'
				sb.append("  and a.company_id like concat(?,'%')   ") ;
				sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.area_code",new String[] {"a.createUid",req.getUinfo().getId()}));
				if("01".equals(req.getUinfo().getUserType())) { //如果是安装人员，那么看自己的单据
					sb.append("  and (b.maintain_id =").append(req.getUinfo().getId()).append(" or b.createUid=").append(req.getUinfo().getId()).append(")" );
				}
			}
			
			sbfrom.append(sb.toString());
			int idx = sbfrom.indexOf(" from ");
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(), req.getPageid(), req.getPagesize(), res,new Object[] { req.getUinfo().getCompanyId() });
			sb.append(" ORDER BY a.createTime desc ") ;
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			res.setRetCode(0);
			res.setRetMessage("OK");
			res.setPage(list);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData projectReport(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "t_project_reporting");
		try {
			param.put("company_id", req.getUinfo().getCompanyId()) ;
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				res =  insertTableData(newg_status, req) ;
				//插入项目记录表
				if(res.getRetCode()!=0) {
					rollback_newg(newg_status);
					return res ;
				}	
			}else {
				param.put("keys", "uuid");
				res =  updateTableData(newg_status, req) ;
			}
			
			
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("操作失败！");
			}
			

		} catch (Exception e) {
			rollback_newg(newg_status);
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
			
		} finally {
			
		}
		return res;
	}
	
	public PageData getProjectReportList(DataReq req) {
		StringBuffer sb = new StringBuffer();
		StringBuffer sbfrom = new StringBuffer();
		List<Map<String, Object>> list = null ;
		PageData res = new PageData();
		try {
			sb.append(" select d.xzqhmc,a.shzt,a.shyj,c.name shztname,a.uuid,company_id,e.companyName,a.project_name,manufacture_id,manufacture_name," +
					"part_a_name,part_a_contact,part_a_contact_phone,part_b_name,part_b_contact,part_b_contact_phone," +
					"area_code,project_address,project_type,investment_scale,build_area,planned_start_time,planned_end_time," +
					"remark,images,createUid,date_format(a.createTime,'%Y-%m-%d %T') createTime," +
					"modifyUid,date_format(a.modifyTime,'%Y-%m-%d %T') modifyTime,b.name project_type_name " +
					" from t_project_reporting a " +
					" INNER JOIN t_param b on a.project_type=b.`value` and b.param_type='92'" +
					" left JOIN t_param c on a.shzt=c.`value` and c.param_type='108'" +
					" left JOIN t_xzqh d on a.area_code=d.xzqhdm  " +
					" left JOIN t_company e on a.company_id=e.companyId ") ;
			sb.append(" where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')   ") ;
			if("01".equals(req.getUinfo().getUserType())) { //如果是安装人员，那么看自己的单据
				sb.append(" and  a.createUid=").append(req.getUinfo().getId()) ;
			}
			if(!StringUtils.isEmpty((String) req.getReqData().get("project_name"))) {
				sb.append(" and a.project_name like '%").append( req.getReqData().get("project_name")).append("%' ");  
			} 
			if(!StringUtils.isEmpty((String) req.getReqData().get("project_type"))) {
				sb.append(" and a.project_type='").append( req.getReqData().get("project_type")).append("' ");  
				
			} 
			if(!StringUtils.isEmpty((String)req.getReqData().get("start_date"))) {
				sb.append("  and a.createTime >=  '").append(req.getReqData().get("start_date")).append("' ");
			}
			if(!StringUtils.isEmpty((String)req.getReqData().get("end_date"))) {
				sb.append("  and a.createTime <=  '").append(req.getReqData().get("end_date")).append(" 23:59:59' ");
			}
			if(!StringUtils.isEmpty((String) req.getReqData().get("shzt"))) {
				sb.append(" and a.shzt='").append( req.getReqData().get("shzt")).append("' ");  
			} 
			if(!StringUtils.isEmpty((String) req.getReqData().get("checkType"))) {
				if("1".equals(req.getReqData().get("checkType"))) {
					sb.append(getChecking(req));
				}
			}
			sbfrom.append(sb.toString());
			int idx = sbfrom.indexOf(" from ");
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(), req.getPageid(), req.getPagesize(), res,new Object[] { req.getUinfo().getCompanyId() });
			sb.append(" ORDER BY a.createTime desc ") ;
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			//System.out.println(sb);
			//System.out.println(req.getUinfo().getCompanyId());
			
			for(int i=0;i<list.size();i++) {
				String images = (String)list.get(i).get("images") ;
				if(!StringUtils.isEmpty(images)) {
					String[] imageList = images.split(";",-1) ;
					List<String> imgURL = new ArrayList<String>() ;
					for(int k=0;k<imageList.length;k++) {
						String str = imageList[k] ;
						imgURL.add(OssUtil.getImgSignURL(str, 0)) ;
						
					}
					list.get(i).put("imageList", imgURL) ;
				}
			}
			res.setRetCode(0);
			res.setRetMessage("OK");
			res.setPage(list);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public StringBuffer getChecking(DataReq req) {
		StringBuffer sbcheck = new StringBuffer();
		Map<String, Object> param = req.getReqData();
		sbcheck.append(" and ( 1=1 ");
		if(!StringUtils.isEmpty((String) param.get("project_name_check"))) {
			sbcheck.append(" and a.project_name like '%").append( req.getReqData().get("project_name_check")).append("%' ");  
		} 
		if(!StringUtils.isEmpty((String) param.get("part_a_name_check"))) {
			sbcheck.append(" and a.part_a_name like '%").append( req.getReqData().get("part_a_name_check")).append("%' ");  
		} 
		if(!StringUtils.isEmpty((String) param.get("part_a_contact_check"))) {
			sbcheck.append(" and a.part_a_contact like '%").append( req.getReqData().get("part_a_contact_check")).append("%' ");  
		} 
		if(!StringUtils.isEmpty((String) param.get("part_a_contact_phone_check"))) {
			sbcheck.append(" and a.part_a_contact_phone like '%").append( req.getReqData().get("part_a_contact_phone_check")).append("%' ");  
		} 
		if(!StringUtils.isEmpty((String) param.get("part_b_name_check"))) {
			sbcheck.append(" and a.part_b_name like '%").append( req.getReqData().get("part_b_name_check")).append("%' ");  
		} 
		if(!StringUtils.isEmpty((String) param.get("part_b_contact_check"))) {
			sbcheck.append(" and a.part_b_contact like '%").append( req.getReqData().get("part_b_contact_check")).append("%' ");  
		} 
		if(!StringUtils.isEmpty((String) param.get("part_b_contact_phone_check"))) {
			sbcheck.append(" and a.part_b_contact_phone like '%").append( req.getReqData().get("part_b_contact_phone_check")).append("%' ");  
		} 
		if(!StringUtils.isEmpty((String) param.get("investment_scale_check"))) {
			sbcheck.append(" and a.investment_scale like '%").append( req.getReqData().get("investment_scale_check")).append("%' ");  
		} 
		if(!StringUtils.isEmpty((String) param.get("xzqhmc_check"))) {
			sbcheck.append(" and d.xzqhmc like '%").append( req.getReqData().get("xzqhmc_check")).append("%' ");  
		} 
		if(!StringUtils.isEmpty((String) param.get("project_address_check"))) {
			sbcheck.append(" and a.project_address like '%").append( req.getReqData().get("project_address_check")).append("%' ");  
		} 
		if(!StringUtils.isEmpty((String) param.get("project_type_check"))) {
			sbcheck.append(" and a.project_type = '").append( req.getReqData().get("project_type_check")).append("' ");  
		} 
		if(!StringUtils.isEmpty((String) param.get("build_area_check"))) {
			sbcheck.append(" and a.build_area like '%").append( req.getReqData().get("build_area_check")).append("%' ");  
		} 
		if(!StringUtils.isEmpty((String) param.get("remark_check"))) {
			sbcheck.append(" and a.remark like '%").append( req.getReqData().get("remark_check")).append("%' ");  
		} 
		sbcheck.append(" ) ");
		return sbcheck;
	}

	public PageData getPointData(DataReq req) {
		StringBuffer sb = new StringBuffer();
		StringBuffer sbfrom = new StringBuffer();
		List<Map<String, Object>> list = null ;
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		try {
			String month = (String)req.getReqData().get("month") ;
			String edate  = DBUtils.getDate(DBUtils.getLastDayOfMonth(month), 0) + " 23:59:59" ;
			String sdate  = month+"-01" ;
			sb.append(" SELECT a.`name`,IFNULL(sum(b.points) ,0) points from t_param a  LEFT JOIN (select point_type,points from t_user_points where  auditStatus='1' and  uid=? and summaryTime>=? and summaryTime<=? ) b on a.`value`=b.point_type where  a.param_type='106'  ") ;
			sb.append(" 		GROUP BY a.`name`  ") ;
			sb.append(" 		ORDER BY a.`value`  ") ;
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getId(),sdate,edate });
			BigDecimal sum = new BigDecimal("0") ;
			for(int i=0;i<list.size();i++) {
				sum = sum.add(new BigDecimal((String) list.get(i).get("points").toString())) ;
			}
			list.get(0).put("sum", sum.toString()) ;
			
			LinkedHashMap<String, Object> extData = new LinkedHashMap<String, Object>() ;
			extData.put("top", list) ;
			
			
			//获取明细数据
			sb.delete(0, sb.length());
			sb.append(" SELECT b.`name`,a.points,a.title,a.sub_title, date_format(a.summaryTime,'%d日 %T') summaryTime,a.link_type,a.link_id from t_user_points a LEFT JOIN t_param b on b.`value`=a.point_type and b.param_type='106' where auditStatus='1' and uid=? and summaryTime>=? and summaryTime<=?   ") ;
			sbfrom.append(sb.toString());
			int idx = sbfrom.indexOf(" from ");
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(), req.getPageid(), req.getPagesize(), res,new Object[] { req.getUinfo().getId(),sdate,edate});
			sb.append(" ORDER BY a.createTime desc ") ;
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getId(),sdate,edate});
			
			res.setExtData(extData);
			res.setRetCode(0);
			res.setRetMessage("OK");
			res.setPage(list);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getLevelParam(DataReq req) throws SQLException {
        StringBuffer sb = new StringBuffer();
        PageData res = new PageData();
        String param_type = "01" ;
        try {
            sb.delete(0, sb.length());
            sb.append(" SELECT `name`,`value`,length(`value`) len from t_param_level where param_type='01' and  length(`value`)=2   ORDER BY  `value` ");
            List<Map<String, Object>> dataList = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] { });
			
			for(int i=0;i<dataList.size();i++) {
				Map<String, Object> map = dataList.get(i) ;
				String preCode = (String) map.get("value") ;
				int len = 4  ;
				
				List<Map<String, Object>> sublist = getList(param_type,preCode,len) ;
				for(int k=0;k<sublist.size();k++) {
					Map<String, Object> e = sublist.get(k) ;
					preCode = (String) e.get("value") ;
					len = 6  ;
					List<Map<String, Object>> sublist2 = getList(param_type,preCode,len) ;
					e.put("list", sublist2) ;
				}
				map.put("list", sublist) ;
			}

			res.setRetCode(0);
			res.setRetMessage("OK");
			res.setPage(dataList);
			
        }catch (Exception e) {
        	e.printStackTrace();
		} finally {
        }

        return res  ;
    }
	
	public List<Map<String, Object>> getList(String param_type,String preCode, int len) throws SQLException {
        StringBuffer sb = new StringBuffer();
        List<Map<String, Object>> dataList = new ArrayList<Map<String,Object>>();
        try {
            sb.delete(0, sb.length());
            sb.append(" SELECT `name`,`value` from t_param_level where param_type='").append(param_type).append("' and `value` like '").append(preCode).append("%'  and length(`value`)=").append(len).append("  ORDER BY  `value`  ");
            dataList = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] { });
        }catch (Exception e) {
        	e.printStackTrace();
		} finally {
        }
        return dataList  ;
        
    }
	
	//获取用户经纬度信息
	public List<Map<String, Object>> getPositionList(String uid,String startdate, String enddate) throws SQLException {
        StringBuffer sb = new StringBuffer();
        List<Map<String, Object>> dataList = new ArrayList<Map<String,Object>>();
        try {
            sb.delete(0, sb.length());
            sb.append(" SELECT `longitude`,`latitude` from t_user_position where  createUid=? and createTime>? and createTime<? order by createTime ");
            dataList = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {uid,startdate,enddate });
        }catch (Exception e) {
        	e.printStackTrace();
		} finally {
        }
        return dataList  ;
        
    }
	
	
	public PageData addPointsRule(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		
		try {
			String rule_name=(String)param.get("rule_name");
			
			JSONArray point_type2=(JSONArray)param.get("point_type2");
			JSONArray point_type4=(JSONArray)param.get("point_type4");
			JSONArray point_type6=(JSONArray)param.get("point_type6");
			JSONArray point_unit=(JSONArray)param.get("point_unit");
			JSONArray points=(JSONArray)param.get("points");
			JSONArray uuid=(JSONArray)param.get("uuid");
			//JSONArray isNeedAudit=(JSONArray)param.get("isNeedAudit");
			
			
			param.put("company_id", req.getUinfo().getCompanyId()) ;
			
			String No=(String)param.get("No");
			if(StringUtils.isEmpty(No)) {
				param.put("No", DBUtils.getDjbh(req.getUinfo().getCompanyId(), "NO")) ;
				for(int i=0;i<point_type2.size();i++) {
					param.put("tableName", "t_user_points_rule");
					param.put("point_type2",(String) point_type2.get(i));
					param.put("point_type4",(String) point_type4.get(i));
					param.put("point_type6",(String) point_type6.get(i));
					param.put("point_unit",(String) point_unit.get(i));
					param.put("points",(String) points.get(i));
					//param.put("isNeedAudit",(String) isNeedAudit.get(i));
					param.put("uuid",uuid.get(i));
					res =  insertTableData(newg_status, req) ;
					if(res.getRetCode()!=0) {
						rollback_newg(newg_status);
						return res ;
					}	
				}
				//插入项目记录表
				if(res.getRetCode()!=0) {
					rollback_newg(newg_status);
					return res ;
				}	
			}else {
				param.put("keys", "uuid");
				for(int i=0;i<point_type2.size();i++) {
					param.put("tableName", "t_user_points_rule");
					param.put("point_type2",point_type2.get(i));
					param.put("point_type4",point_type4.get(i));
					param.put("point_type6",point_type6.get(i));
					param.put("point_unit",point_unit.get(i));
					param.put("points",points.get(i));
					//param.put("isNeedAudit",(String) isNeedAudit.get(i));
					param.put("uuid",uuid.get(i));
					res =  updateTableData(newg_status, req) ;
					if(res.getRetCode()!=0) {
						rollback_newg(newg_status);
						return res ;
					}	
				}
			}
			
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
			}else{
				res.setRetCode(9);
				res.setRetMessage("操作失败！");
				rollback_newg(newg_status);
			}

		} catch (Exception e) {
			rollback_newg(newg_status);
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
			
		} finally {
			
			
		}
		return res;
	}
	
	public PageData getRuleList(DataReq req) {
		StringBuffer sb = new StringBuffer();
		StringBuffer sbfrom = new StringBuffer();
		StringBuffer condition = new StringBuffer();
		List<Map<String, Object>> list = null ;
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		try {
			List<Object> pm = new ArrayList<Object>();
			pm.add(req.getUinfo().getCompanyId());
			String No = (String) param.get("No") ;
			if(!StringUtils.isEmpty(No)) {
				pm.add(No) ;
				condition.append(" and  a.`No` = ? ") ;
			}
			sb.append(" SELECT a.isNeedAudit,a.uuid,a.rule_name,a.`No`,a.point_type2,a.point_type4,a.point_type6,a.point_unit,a.points,a.createUid,b.`name` point_type2_name,c.`name` point_type4_name,d.`name` point_type6_name,u1.nickName,date_format(a.createTime,'%Y-%m-%d %T') createTime from t_user_points_rule a  ") ;
			sb.append(" INNER JOIN t_param_level b on a.point_type2=b.`value` and b.param_type='01'  ") ;
			sb.append(" INNER JOIN t_param_level c on a.point_type4=c.`value` and c.param_type='01'  ") ;
			sb.append(" INNER JOIN t_param_level d on a.point_type6=d.`value` and d.param_type='01'  ") ;
			sb.append(" INNER JOIN t_user u1 on a.createUid=u1.id  ") ;
			sb.append("  where a.company_id like concat(?,'%')  ") ;
			sb.append(condition) ;
			
			sbfrom.append(sb.toString());
			int idx = sbfrom.indexOf(" from ");
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(), req.getPageid(), req.getPagesize(), res,pm.toArray());
			sb.append(" ORDER BY a.createTime desc ") ;
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							pm.toArray());
		
			res.setRetCode(0);
			res.setRetMessage("OK");
			res.setPage(list);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getPointList(DataReq req) {
		StringBuffer sb = new StringBuffer();
		StringBuffer sbfrom = new StringBuffer();
		StringBuffer condition = new StringBuffer();
		List<Map<String, Object>> list = null ;
		PageData res = new PageData();
		try {
			String startDate = (String)req.getReqData().get("startDate") ;
			String endDate = (String)req.getReqData().get("endDate") ;
	
			List<Object> pm = new ArrayList<Object>();
			pm.add(req.getUinfo().getCompanyId());
			if(!StringUtils.isEmpty(startDate)) {
				pm.add(startDate) ;
				condition.append(" and summaryTime>=? ") ;
			}
			if(!StringUtils.isEmpty(endDate)) {
				pm.add(endDate) ;
				condition.append(" and summaryTime<=? ") ;
			}
			
			//获取明细数据
			sb.delete(0, sb.length());
			sb.append("SELECT a.uuid,a.rule_uuid,a.basenum,b.`name`,a.points,a.title,a.sub_title, date_format(a.summaryTime,'%Y-%m-%d %T') summaryTime,a.link_type,a.link_id,a.auditStatus,a.auditComments,a.auditUid,a.uid,u1.nickName username,u2.nickName auditName,pb.`name` point_type2_name,pc.`name` point_type4_name,pd.`name` point_type6_name,rule.point_unit,rule.points rule_point from t_user_points a ") ;
			sb.append(" INNER JOIN t_user u1 on u1.id = a.uid") ;
			
			sb.append(" INNER JOIN t_user_points_rule rule ON rule.uuid = a.rule_uuid ") ;
			
			sb.append(" INNER JOIN t_param_level pb on rule.point_type2=pb.`value` and pb.param_type='01'  ") ;
			sb.append(" INNER JOIN t_param_level pc on rule.point_type4=pc.`value` and pc.param_type='01'  ") ;
			sb.append(" INNER JOIN t_param_level pd on rule.point_type6=pd.`value` and pd.param_type='01'  ") ;
			
					
			sb.append(" LEFT JOIN t_user u2 on u2.id = a.auditUid") ;
			sb.append(" LEFT JOIN t_param b on b.`value`=a.point_type and b.param_type='106' ") ;
			sb.append("  where u1.companyId like concat(?,'%')  ") ;
			sb.append(condition) ;

			sbfrom.append(sb.toString());
			int idx = sbfrom.indexOf(" from ");
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(), req.getPageid(), req.getPagesize(), res,pm.toArray());
			sb.append(" ORDER BY a.createTime desc ") ;
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							pm.toArray());
			res.setRetCode(0);
			res.setRetMessage("OK");
			res.setPage(list);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	
	//审核积分
	public PageData audituserPoint(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "t_user_points");
		param.put("keys", "uuid");
		try {
			String uuid=(String)param.get("uuid");
			String auditStatus=(String)param.get("auditStatus");
			String auditComments=(String)param.get("auditComments");
			
			if(StringUtils.isEmpty(uuid)) {
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("单据ID不能为空");
				return res ;
			}
			
			if(StringUtils.isEmpty(auditStatus)) {
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("审核状态不能为空");
				return res ;
			}
			
			param.put("auditTime", DBUtils.getCurTime()) ;
			param.put("auditUid", req.getUinfo().getId()) ;
			res =  updateTableData(newg_status, req) ;
			
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("操作失败！");
			}

		} catch (Exception e) {
			rollback_newg(newg_status);
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
			
		} finally {
			
			
		}
		return res;
	}
	
	//审核积分
	public PageData addUserPosition(DataReq req) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		Map<String, Object> param = req.getReqData();
		try {
			param.put("distance", "0") ;
			sb.append(" select latitude,longitude from t_user_position where createUid=? order by id desc limit 1 ") ;
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getId() });
			if(list.size()>0) {
				BigDecimal last_longitude = new BigDecimal(list.get(0).get("longitude").toString()) ;
				BigDecimal last_latitude = new BigDecimal(list.get(0).get("latitude").toString()) ;
				
				BigDecimal longitude = new BigDecimal((String)req.getReqData().get("longitude")) ; 
				BigDecimal latitude = new BigDecimal((String)req.getReqData().get("latitude")) ; 
				double d = DBUtils.GetDistance(last_longitude.doubleValue(), last_latitude.doubleValue(), longitude.doubleValue(), latitude.doubleValue()) ;
				if(d<100) {
					//res.setRetCode(0);
					//res.setRetMessage("距离小于100米不记录");
					//return res ;
				}
				param.put("distance",String.valueOf(d)) ;
			}
			
			newg_status = begin_newg();// 开启事物
			param.put("tableName", "t_user_position");
			res =  insertTableData(newg_status, req) ;
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("操作失败！");
			}

		} catch (Exception e) {
			rollback_newg(newg_status);
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {
			
			
		}
		return res;
	}
	
	@SuppressWarnings({ "unchecked" })
	public PageData saveProjectReportShzt(DataReq req) {
		PageData res = new PageData();
		Map<String, Object> param = req.getReqData();
		PageData sqlInfo = null;
		try {
			newg_status = begin_newg();// 开启事物
			param.put("tableName", "t_project_reporting");
			param.put("keys", "uuid") ;
			sqlInfo = getUpdateSQL(req);
			if (sqlInfo.getRetCode() != 0) {
				rollback_newg(newg_status);
				return sqlInfo;
			}
			final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
			this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());
			commit_newg(newg_status);
			res.setRetCode(0);
			res.setRetMessage("OK");
			res.setKey(param.get("uuid").toString());
		} catch (Exception e) {
			rollback_newg(newg_status);
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {
			
				}
		return res;
	}
	
	public PageData getDevList(DataReq req) {
		StringBuffer sb = new StringBuffer();
		List<Map<String, Object>> list = null ;
		PageData res = new PageData();
		Connection conn = null ;
		try {
			conn = DBUtils.getDBConn_Ope();
			String project_uuid = (String)req.getReqData().get("project_uuid") ;
			
			sb.delete(0, sb.length());
			sb.append(" SELECT a.wx_id,b.xmlxr,b.xmdz,b.xmlxrdh,a.sbxlh,c.sbxh,date_format(a.cjsj ,'%Y-%m-%d') install_date,date_format(a.expired_date ,'%Y-%m-%d') expired_date  from is_equipment_info a INNER JOIN is_install_info b on a.dd_id=b.dd_id INNER JOIN t_sblx c on a.sblx_id=c.sblx_id where a.sjly='agent' and a.dd_id=? ") ;
			sb.append(" ORDER BY a.cjsj desc ") ;
			list = this.queryForList(conn,sb.toString(),new Object[] {project_uuid});
			
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn!=null)
				try {
					conn.close();
				} catch (SQLException e) {
				}
		}
		return res;
	}
	
	public PageData installDev(DataReq req) {
		Map<String, Object> param = req.getReqData();
		HashMap<String, Object> param_dev = new LinkedHashMap<String, Object>();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		try {
			param_dev.putAll(param);
			
			//生成项目
			param.put("tableName", "t_project");
			param.put("project_id", DBUtils.getDjbh(req.getUinfo().getCompanyId(), "PRO")) ;
			param.put("company_id", req.getUinfo().getCompanyId()) ;
			param.put("project_name", param.get("contact")) ;
			param.put("maintain_id", req.getUinfo().getId()) ;
			
			if(StringUtils.isEmpty((String)param.get("contact"))) {
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("联系人不能为空！");
				return res ;
			}
			
			if(StringUtils.isEmpty((String)param.get("contact_phone"))) {
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("联系电话不能为空！");
				return res ;
			}
			
			if(StringUtils.isEmpty((String)param.get("longitude"))) {
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("经度不能为空！");
				return res ;
			}
			
			
			if(StringUtils.isEmpty((String)param.get("latitude"))) {
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("纬度不能为空！");
				return res ;
			}
			
			if(StringUtils.isEmpty((String)param.get("sblx_id"))) {
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("设备型号不能为空！");
				return res ;
			}
			
			
			if(StringUtils.isEmpty((String)param.get("manufacture_id"))) {
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("厂家不能为空！");
				return res ;
			}
			
			if(StringUtils.isEmpty((String)param.get("project_address"))) {
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("项目地址不能为空！");
				return res ;
				
			}
			
			param.put("planned_start_time", DBUtils.getCurTime()) ;
			param.put("planned_end_time", DBUtils.getCurTime()) ;
			param.put("investment_scale", null) ; //投资规模
			param.put("project_status", "07") ;
			
			param.put("charge", param.get("contact")) ;
			if(StringUtils.isEmpty((String)param.get("area_code"))) {
				JSONObject addressJson = MapUtil.getGeoByAddress((String)param.get("project_address")) ;
				param.put("area_code", addressJson.get("adcode")) ;
				param_dev.put("area_code", addressJson.get("adcode")) ;
			}
			param.put("service_mode", "01") ;
			param.put("project_type", "01") ;
			param.put("project_level", "01") ;

			
			res =  insertTableData(newg_status, req) ;
			if(res.getRetCode()!=0) {
				rollback_newg(newg_status);
				return res ;
			}
			param.put("project_uuid", res.getKey()) ;
			param_dev.put("project_uuid", res.getKey()) ;
			
			HashMap<String,Object> e = new LinkedHashMap<String, Object>();
			e.put("tableName", "t_project_record");
			e.put("project_uuid", res.getKey());
			e.put("project_status", "07");
			e.put("remark", "完成");
			req.setReqData(e);
			PageData resTmp = insertTableData(newg_status, req) ;
			if(resTmp.getRetCode()!=0) {
				rollback_newg(newg_status);
				return resTmp ;
			}
			
			//项目排期
			e = new LinkedHashMap<String, Object>();
			e.put("tableName", "t_project_scheduling");
			e.put("project_uuid", res.getKey());
			e.put("start_time", DBUtils.getCurTime());
			e.put("end_time", DBUtils.getCurTime());
			req.setReqData(e);
			resTmp = insertTableData(newg_status, req) ;
			if(resTmp.getRetCode()!=0) {
				rollback_newg(newg_status);
				return resTmp ;
			}
			
			//创建工单
			e.put("tableName", "t_work_order_info");
			e.put("work_order_id", DBUtils.getDjbh(req.getUinfo().getCompanyId(), "WOR")) ;
			e.put("company_id", req.getUinfo().getCompanyId()) ;
			e.put("dd_id", param.get("project_uuid"));
			e.put("work_type_id", "1");
			e.put("contact", param.get("contact"));
			e.put("contact_phone", param.get("contact_phone"));
			e.put("address", param.get("project_address"));
			e.put("planned_start_time", null);
			e.put("planned_end_time", null);
			e.put("xzqhdm", param.get("area_code"));
			e.put("manufacture_id", param.get("manufacture_id"));
			e.put("describe", "创建安装工单");
			e.put("status", "06");
			e.put("maintain_id", req.getUinfo().getId()) ;
			req.setReqData(e);
			resTmp = insertTableData(newg_status, req) ;
			if(resTmp.getRetCode()!=0) {
				rollback_newg(newg_status);
				return resTmp ;
			}
			
			//插入工单记录
			e.put("describe", "工单完成"); //待接单
			resTmp = addWorkOrderRecord(req) ;
			if(resTmp.getRetCode()!=0) {
				rollback_newg(newg_status);
				return resTmp ;
			}
			
			
			//添加设备
			req.setReqData(param_dev);
			res = addOpeDev(req) ;
			if(res.getRetCode()!=0) {
				rollback_newg(newg_status);
				return res ;
			}
			
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("操作失败！");
			}
		} catch (Exception e) {
			rollback_newg(newg_status);
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	
	
}